Help

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

Solved
Jump to Solution
1100 5
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

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}, ',', '&' )

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}
    )
)

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.

This works, thank you so much!