May 17, 2019 08:39 AM
Currently I have a field that contains multiple products separated by commas.
Is there a way to “find” all commas and “replace” them with the “/n” line break?
I would like the product field which currently looks like this
“product1, product2, product3, product4”
to appear in the field as
product1
product2
product3
product4
Is there a way to do this?
May 17, 2019 10:46 AM
Add a formula field using this formula: SUBSTITUTE({Product Field},",","\n")
Aug 11, 2019 05:49 PM
Does not work, I get an error.
Aug 11, 2019 09:09 PM
Don’t know what to tell you, as that is the proper formula. Did you replace ‘Product Field
’ with the name of your actual field? When you copied the formula, are there straight quotes ("
) or curly quotes?
Aug 12, 2019 09:17 AM
Yes, I did, I tried both kinds of quotes.
Feb 10, 2020 10:44 PM
Did anyone solve this? I also get error. Situation arises when I am trying to put a line break between items that have come from a lookup.
Feb 10, 2020 11:56 PM
The formula works as given (there should be a space after the comma, otherwise you’ll get an extra blank space before every new line).
SUBSTITUTE({Rollup of Link},", ","\n")
I’ve set up a demo in the “Linebreak Test” table.
Explore the "Formula Testing" base on Airtable.
The only other issue I can imagine is that you’re running into an issue where the commas aren’t actually there (that is, rather than a string that includes commas, you have an array, which Airtable displays with commas separating the array elements). If that’s the case, try:
ARRAYJOIN({Product Field},"\n")
or
SUBSTITUTE({Product Field}&"",", ","\n")
I haven’t tested these two formulas, since I can’t seem to reproduce the behaviour.
Mar 25, 2020 08:36 AM
Thank you for steering me in the right direction and providing the Formula Testing base. It was good to see the formula working.
I ended up using this formula to make it function in my base:
SUBSTITUTE(ARRAYJOIN({Earned Rollup},", “),”, “,”\n")
I noticed this blog post did not include the space after the comma plus it did not work for me as described:
SUBSTITUTE({Rollup Field},",","\n")
This works just like any basic substitution but takes the comma that separates each value in your {Rollup Field} (whatever you named it contained in {curly braces}) and replaces it with \n . To a computer, \n means “new line” and so this formula will put each of the combined values in a rollup field on its own line and remove the commas to provide a more human-readable format.
Thanks again :slightly_smiling_face:
Sep 14, 2020 07:53 PM
I am trying to do something similar, but I want the array to only pull in unique items. When I use this formula on a Unique Array rollup field I get an error. Is there anyway to no show duplicated in the substitution field?
Sep 16, 2020 06:10 PM
Hmm, which formula are you using? There were two in my post, perhaps the other one would work?
ARRAYJOIN({Product Field},"\n")
or
SUBSTITUTE({Product Field}&"",", ","\n")
Sep 21, 2020 06:57 PM
The roll up field is using
ARRAYJOIN(values)
and the field that is suppose to make it human readable is using
SUBSTITUTE({Items Purchased},",","\n")
If I make it ArrayUnique then I get an error in the Substitute field
Sep 21, 2020 07:59 PM
@Opher_Yunger Are you using a rollup field and want each unique value on its own line? If so, try this formula in the rollup:
ARRAYJOIN(ARRAYUNIQUE(values), "\n")
The inner ARRAYUNIQUE
returns only unique values. Then ARRAYJOIN
joints the values into a text string joined with the specified character.
Sep 22, 2020 11:18 AM
That was way easier. Thanks so much!
Is there a way to Alphabetize the Array?
Sep 22, 2020 12:13 PM
No, there is no array function to sort an array. The elements are in the same order as the linked record field itself. You can alphabetize the linked records according to the primary field for the linked records using the bulk update block/app.
Jan 08, 2022 02:42 AM
@kuovonne I have the opposite challenge! I’d like to get a text field with URLs that now looks like this:
https://www.google.com,
https://www.facebook.com,
https://www.amazon.com
Which I’d like to replace the “,” with a “;” like this:
https://www.google.com;
https://www.facebook.com;
https://www.amazon.com
Super simple I’d say but its seems to behave differently than excel/numbers…
May 12, 2022 07:58 PM
I’ve been looking for this solution for ages. Thanks so much. You are and always will be a superstar!