Concatenate multiple fields separated by commas without string of commas at end

[shelby_jackson]

Hello! I am currently using the below formula to pull together a list of cities that correspond to a record. I have City 1, City 2, City 3, City 4, City 5, Cities 6 fields. Formula looks like this:

CONCATENATE({BP City 1}, ", ",{BP City 2}, ", ",{BP City 3}, ", ",{BP City 4}, ", ",{BP City 5}, ", ",{BP Cities 6})

My formula works if all 6 fields contain cities although it doesn’t look great if only one city is listed. It ends up looking like this:

Philadelphia, , , , ,

Anyone know how to fix the formula so that the appropriate number of commas appear so there will not be a string of messy commas at the end?

Thank you!

[kuovonne · Accepted solution]

Getting Values from 4 columns Ask the community

Welcome back to the Airtable community! There are multiple ways of doing this, especially getting the commas and spaces right. Here is my current favorite methods. REGEX_REPLACE( CONCATENATE( IF({A}, {A} & ", "), IF({B}, {B} & ", "), IF({C}, {C} & ", "), IF({D}, {D}) ), ", $", "" )

[shelby_jackson]

Thank you so much, @kuovonne! :heart_eyes:

[KFIL]

So helpful!  Stumbled across this and it worked like a charm.... 🙂