Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 15, 2021 05:16 AM
I have a lookup field called “Jobs” that returns fields from a linked record. In my case these are job descriptions.
It shows them as a comma separated list, e.g. “CEO, director, manager…”.
I would like to convert the commas into line-breaks with a formula. But SUBSTITUTE(Jobs,", “,”\n") returns an #ERROR!
What am I doing wrong?
Solved! Go to Solution.
Dec 15, 2021 06:28 AM
Double quotes aren’t the problem. Airtable recognizes both double and single quotes in formulas, though I prefer double quotes because only when using those does it recognize when you’re inside a string vs outside a string (for autocomplete features).
That won’t work either, and here’s why: lookup fields most often return an array, not a single data item. While it displays commas between items in the field, those are there for display only. They don’t actually exist in the data. When concatenating an array with an empty string (as @Achim_Baur did above), the result is a mashed-together string of all items with no separators.
@Achim_Baur The most compact solution is to turn your lookup field into a rollup field. Rollup fields let you execute a formula (called the aggregation formula) on the array of returned items. In this case, your aggregation formula could be this:
ARRAYJOIN(values, "\n")
The ARRAYJOIN()
function joins the items in the array with the provided string as a separator.
Dec 15, 2021 05:40 AM
Hi Achim. Change all of your different double quotes to single quotes and you should be good!
Dec 15, 2021 05:47 AM
Thanks, but that did not work.
I think somehow the lookup field “Jobs” might not be a real text string?
If I change my formula to Jobs & ''
I get CEOdirectormanager
. This is a real text string now. But of course I don’t have any commas now that I could replace with a line break.
Dec 15, 2021 06:24 AM
Try SUBSTITUTE(Jobs&’’,’,’,’\n’). Concat an empty string to it before the substitute.
Dec 15, 2021 06:28 AM
Thanks but that also returns CEOdirectormanager
.
Dec 15, 2021 06:28 AM
Double quotes aren’t the problem. Airtable recognizes both double and single quotes in formulas, though I prefer double quotes because only when using those does it recognize when you’re inside a string vs outside a string (for autocomplete features).
That won’t work either, and here’s why: lookup fields most often return an array, not a single data item. While it displays commas between items in the field, those are there for display only. They don’t actually exist in the data. When concatenating an array with an empty string (as @Achim_Baur did above), the result is a mashed-together string of all items with no separators.
@Achim_Baur The most compact solution is to turn your lookup field into a rollup field. Rollup fields let you execute a formula (called the aggregation formula) on the array of returned items. In this case, your aggregation formula could be this:
ARRAYJOIN(values, "\n")
The ARRAYJOIN()
function joins the items in the array with the provided string as a separator.
Dec 15, 2021 06:37 AM
Thank you so very much. That did the trick!
Thank you also for the explanation. It helps me understand what is going on here.
Dec 10, 2022 07:05 AM
thanks so much for this!
Airtable has a problem - when we type in the arrayjoin() formula, it does not show that it accepts a 2nd paramter which allows us to modify the separator from a comma to whatever we want.
this is what i did after reading your fantastic answer
ARRAYJOIN(values, "\n" & REPT("-",50) & "\n")