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 &..
Solved
Replace "," by "&" in a lookup field
+3Best answer by Alexey_Gusev
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.
+25Hi,
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}, ',', '&' )+3Hi,
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.
+22I 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}
)
)+3As 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.
+25I 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.
+3Hi,
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!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.