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.

Replace "," by "&" in a lookup field

Topic Labels: Formulas
Solved
Jump to Solution
2484 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Yvia
6 - Interface Innovator
6 - Interface Innovator

Hi there! I've been trying to replace  "," by "&" in a lookup field using Substitute and IF statements but I still receive an error or the result is not giving me what I want. So the result I want is IF First name contains  "," then replace by &.. 

Yvia_0-1679407184729.png

 

1 Solution

Accepted Solutions

Hi,

my fault. I didn't test and forget that comma isn't part of text data. 
Then I would turn the lookup field to rollup with Aggregation formula

SUBSTITUTE(
  ARRAYJOIN(values),
  ',' ' & ')
You can put spaces around  &  according to your needs.

See Solution in Thread

6 Replies 6
Alexey_Gusev
13 - Mars
13 - Mars

Hi,

Lookup always return array of values (sometimes it's array with a single element or empty array). In formula you should convert it to string by CONCATENATE  or add empty string to it  

SUBSTITUTE(
  CONCATENATE({First Name}), ',', '&' )

or

SUBSTITUTE(
  ''&{First Name}, ',', '&' )
Yvia
6 - Interface Innovator
6 - Interface Innovator

I tried the two formulas but didn't get the desired result and got this CourtneyRyan instead of Courtney & Ryan. 

As my first recommendation, I would advise that you get rid of the lookup field and swap in a rollup field that pulls the values of the First Name field. You can then toss in a formula like this:

IF(
    values,
    IF(
        COUNTALL(values) > 1,
        TRIM(
            REGEX_REPLACE(
                "" & values,
                ",\s?", " & "
            )
        )
    ),
    values
)

If you want to keep your lookup field and do this in a new formula field, you can use something like this:

IF(
    {First Name},
    IF(
        REGEX_MATCH(
            "" & {First Name},
            ","
        ),
        TRIM(
            REGEX_REPLACE(
                "" & {First Name},
                ",\s?", " & "
            )
        ),
        "" & {First Name}
    )
)
Yvia
6 - Interface Innovator
6 - Interface Innovator

Thank you for your suggestions but didn't work as well. 

 

Hi,

my fault. I didn't test and forget that comma isn't part of text data. 
Then I would turn the lookup field to rollup with Aggregation formula

SUBSTITUTE(
  ARRAYJOIN(values),
  ',' ' & ')
You can put spaces around  &  according to your needs.
Yvia
6 - Interface Innovator
6 - Interface Innovator

This works, thank you so much!