Help

Re: How to replace commas with line breaks in a field

4443 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Ron_Daniell
4 - Data Explorer
4 - Data Explorer

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?

15 Replies 15

Add a formula field using this formula: SUBSTITUTE({Product Field},",","\n")

Does not work, I get an error.

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?

Yes, I did, I tried both kinds of quotes.

Paul_Bridgman
4 - Data Explorer
4 - Data Explorer

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.

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.

Formula Testing - Airtable

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.

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:

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?

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

Opher_Yunger
5 - Automation Enthusiast
5 - Automation Enthusiast

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

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

That was way easier. Thanks so much!
Is there a way to Alphabetize the Array?

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.

Jord1
4 - Data Explorer
4 - Data Explorer

@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…

I’ve been looking for this solution for ages. Thanks so much. You are and always will be a superstar!