Help

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

Solved
Jump to Solution
665 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Robert_Paige
5 - Automation Enthusiast
5 - Automation Enthusiast

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
kuovonne
18 - Pluto
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.

See Solution in Thread

4 Replies 4
kuovonne
18 - Pluto
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.

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!