Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

How to force underscores between Lookup Text

Topic Labels: Formulas
Solved
Jump to Solution
1167 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Brooke_Lutz
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

1 Solution

Accepted Solutions
Jack_Manuel
7 - App Architect
7 - App Architect

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

Screenshot 2024-05-21 at 16.49.46.png

The formula:

 

 

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

 

 

 

 

See Solution in Thread

3 Replies 3
Jack_Manuel
7 - App Architect
7 - App Architect

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

Screenshot 2024-05-21 at 16.49.46.png

The formula:

 

 

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

 

 

 

 

Brooke_Lutz
5 - Automation Enthusiast
5 - Automation Enthusiast

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
7 - App Architect
7 - App Architect

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