Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

# How to merge many fields with separators, but remove separators when cell is blank?

Topic Labels: Formulas
Solved
305 4
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Hello Airtable Community,

I am new to Airtable and I am struggling with a simple CONCATENATE formula:

The DESIRED RESULT column is what I am trying to achieve.

You can see that anytime I try to add “" as separators, but if one of the cells is blank then not to have the "” in it’s place.

I tried this formula:

``````CONCATENATE(CompName,"_", Length,"_", Dimensions,"_", Date,"_", Code)
``````

Not sure if CONCATENATE is the right solve.

I have been able to do this in Google Sheets with this formula:

``````=JOIN("_",FILTER(B2:F2,B2:F2<>""))
``````

Been racking my brain for a whole week on this. I appreciate any help or guidance.

1 Solution

Accepted Solutions
18 - Pluto

Welcome to the Airtable community!

Unlike Google Sheets, Airtable formulas only can do array functions in rollup situations.

If you will always have the first field, you can use this format

``````CONCATENATE(
{CompName},
IF( {Length}, "_" & {Length}),
IF( {Dimensions}, "_" & {Dimensions}),
IF( {Date}, "_" & {Date}),
IF( {Code}, "_" & {Code})
)
``````

This doesn’t match line 5 in your screen shot of desired results, but maybe you didn’t fill in that row?

If even the first field is optional, I have a different formula that can handle that situation that I presented at the DareTable 2022 conference, but I am away from a computer and and I don’t remember it off the top of my head because it involves regular expressions.

4 Replies 4
18 - Pluto

Welcome to the Airtable community!

Unlike Google Sheets, Airtable formulas only can do array functions in rollup situations.

If you will always have the first field, you can use this format

``````CONCATENATE(
{CompName},
IF( {Length}, "_" & {Length}),
IF( {Dimensions}, "_" & {Dimensions}),
IF( {Date}, "_" & {Date}),
IF( {Code}, "_" & {Code})
)
``````

This doesn’t match line 5 in your screen shot of desired results, but maybe you didn’t fill in that row?

If even the first field is optional, I have a different formula that can handle that situation that I presented at the DareTable 2022 conference, but I am away from a computer and and I don’t remember it off the top of my head because it involves regular expressions.

5 - Automation Enthusiast

Thank you @kuovonne!

I tried your Formula and it’s gotten me closer, but now I have a “_” aways at the end of the output.

Appreciate the help!

18 - Pluto

That’s because I have yet to learn to stop typing formulas on my phone. (But the alternative is usually to not answer the question at all because when I’m back at a computer, I may not have the time, compared to when I’m sitting around a theater during an unexpected intermission due to technical difficulties.)

I’ve edited the formula above to re-arrange a few things. Please try again.

5 - Automation Enthusiast

Amazing! @kuovonne
I think you need to keep doing what you are doing, phone or computer.

I appreciate the help!