Skip to main content

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

 

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

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. 

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

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. 

 

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


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.

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! 

Reply


Terms & Conditions