Skip to main content
Solved

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

  • April 24, 2022
  • 4 replies
  • 59 views
R

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.

Best answer by kuovonne

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

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • Answer
  • April 24, 2022

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.

R
  • Robert_Paige
  • Author
  • New Participant
  • April 25, 2022

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.

Appreciate the help!

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • April 25, 2022

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!


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.

R
  • Robert_Paige
  • Author
  • New Participant
  • April 27, 2022

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!

kuovonne
Terms & ConditionsAccessibility statement