How to force underscores between Lookup Text

[Brooke_Lutz]

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!

[Jack_Manuel · Accepted solution]

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

The formula:

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

[Brooke_Lutz]

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!

[Jack_Manuel]

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, "_")