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

[Robert_Paige]

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.

[kuovonne · Accepted solution]

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.

[Robert_Paige]

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:

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

[kuovonne]

Robert_Paige:

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

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.

[Robert_Paige]

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

I appreciate the help!