Skip to main content
Solved

How to force underscores between Lookup Text

B
Forum|alt.badge.img+4

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!

Best answer by Jack_Manuel

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

The formula:

 

 

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

 

 

 

 

View original
Did this topic help you find an answer to your question?

3 replies

J
Forum|alt.badge.img+15
  • Jack_Manuel
  • Inspiring
  • 75 replies
  • Answer
  • May 21, 2024

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

The formula:

 

 

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

 

 

 

 

B
Forum|alt.badge.img+4
  • Brooke_Lutz
  • Author
  • New Participant
  • 3 replies
  • May 21, 2024
Jack_Manuel wrote:

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!

J
Forum|alt.badge.img+15
  • Jack_Manuel
  • Inspiring
  • 75 replies
  • May 22, 2024

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, "_")
B
1 person likes this
  • B
    Brooke_Lutz

Reply


Most helpful members this week

Terms & Conditions