Help

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
Jump to Solution
305 4
cancel
Showing results for 
Search instead for 
Did you mean: 

Hello Airtable Community,

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

Screen Shot 2022-04-24 at 11.18.44 AM

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

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.

See Solution in Thread

4 Replies 4

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.

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.

Screen Shot 2022-04-25 at 8.18.19 AM

Appreciate the help!

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.

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

I appreciate the help!