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

[Yvia]

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 &.. 

[Alexey_Gusev]

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]

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

[Ben_Young1]

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]

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

[Alexey_Gusev · Accepted solution]

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]

This works, thank you so much!