+13I 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?
Best answer by Justin_Barrett
Hi Achim. Change all of your different double quotes to single quotes and you should be good!
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.
+13Thanks, 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.
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.
Try SUBSTITUTE(Jobs&’’,’,’,’\n’). Concat an empty string to it before the substitute.
+13Try SUBSTITUTE(Jobs&’’,’,’,’\n’). Concat an empty string to it before the substitute.
Thanks but that also returns CEOdirectormanager.
+21Hi Achim. Change all of your different double quotes to single quotes and you should be good!
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.
+13Double 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.
Thank you so very much. That did the trick!
Thank you also for the explanation. It helps me understand what is going on here.
+6Double 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.
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")
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.