Skip to main content

Hello! I am trying to format my data in a very specific way and require underscores between the text. I know when using a Lookup and formula there are challenges because the Lookup format is not a string and the commas within the field do not actually exist. I tried converting the field to a string and using a substitute formula but that just removed the commas and spaces from the field. 

  • Current Lookup Format: pa, nv, fl
  • Substitute Format: panvfl
  • Desired Format: pa_nv_fl

Does anyone know if there is a formula to add underscores to a lookup field that contains multiple options?

Thank you in advance and I appreciate any support in solving this!

Substitute method works for me on lookup field. Unless I'm misunderstanding you.

The formula:

 

 

SUBSTITUTE(
{lookup},
", ",
"_"
)

 

 

 

 

Substitute method works for me on lookup field. Unless I'm misunderstanding you.

The formula:

 

 

SUBSTITUTE(
{lookup},
", ",
"_"
)

 

 

 

 


Hi Jack! Thank you so much for your response! The column type of the lookup I was referencing was "Single line text" which would pull my data in with commas and cause an #ERROR! when I used that formula. I just switched the column type over to "Single select" and that did the trick. Sincerely appreciate your help with this!

Glad that helped but you had me wondering how you would do this with a "Single line text" field instead.

Best I could come up with was to use a Rollup, instead of a Lookup field, with the formula:

ARRAYJOIN(values, "_")

Reply


Terms & Conditions