Skip to main content

Suppress comma delimiter when no data in fields

  • March 25, 2019
  • 7 replies
  • 90 views
P

I’m having problems suppressing extraneous comma delimiters when trying to combine multiple fields into a single column of values separated by commas, but showing no comma if any of the combined fields are blank. I tried various combinations of this formula to no avail.

IF({Racer 1 Full Name},{Racer 1 Full Name})&IF({Racer 2 Full Name},", "&{Racer 2 Full Name})&IF({Racer 3 Full Name},", "&{Racer 3 Full Name})&IF({Racer 4 Full Name},", "&{Racer 4 Full Name})&IF({Racer 5 Full Name},", "&{Racer 5 Full Name})

The above formula yields the following output:

The above example is trying to place 1 to 5 names in a single field separated by commas, if the name field being combined is blank, there should be no comma separator. Note, there is always a Racer 1 name so no need to suppress on that field, just on Racers 2 through 5).

Thanks for any workarounds offered. Not sure if I’m missing something simple here or not and confused why the basic IF statement is not working that way it seemingly should.

I guess I should add that each “FullName” field is also a formula field in the above calculation. In other words, each person’s (racer) name is a formula like this:
{Racer Name (First)} & " " & {Racer Name (Last)}

7 replies

D
Forum|alt.badge.img+14

Your formula works when I try it.

P
  • Peter_Nelson
  • Author
  • Participating Frequently
  • March 25, 2019

Your formula works when I try it.


David, I modified by question and issue (above). Each name in the series I am trying to string together is formula field that calculates the Full Name as a combination of separate fields for First Name and Last Name. I would think the IF formula should work for that too but it’s not for me. Here’s an example of the output not suppressing the commas when the 2nd racer name is blank.

D
Forum|alt.badge.img+14

I see, there seems to be a problem with Airtable recognising the concatenated name fields as blank. This might be a bug.

A workaround seems to be to replace the logical condition with LEN{Racer 2 Full Name}>1

IF(LEN{Racer 1 Full Name}>1},{Racer 1 Full Name})&IF(LEN{Racer 2 Full Name}>1,", "&{ etc.

Justin_Barrett
Forum|alt.badge.img+21

David, I modified by question and issue (above). Each name in the series I am trying to string together is formula field that calculates the Full Name as a combination of separate fields for First Name and Last Name. I would think the IF formula should work for that too but it’s not for me. Here’s an example of the output not suppressing the commas when the 2nd racer name is blank.


The reason your formula is catching the supposedly-empty fullname fields is because the space makes it non-empty. You could modify the name-combination formulas like so, only combining the names if the first name actually exists:

IF({Racer Name (First)}, {Racer Name (First)} & " " & {Racer Name (Last)})

If there’s no first name, then the combo field would truly be blank, and it wouldn’t be picked up by your longer formula.

D
P
  • Peter_Nelson
  • Author
  • Participating Frequently
  • March 25, 2019

I see, there seems to be a problem with Airtable recognising the concatenated name fields as blank. This might be a bug.

A workaround seems to be to replace the logical condition with LEN{Racer 2 Full Name}>1

IF(LEN{Racer 1 Full Name}>1},{Racer 1 Full Name})&IF(LEN{Racer 2 Full Name}>1,", "&{ etc.


David, I too thought this might be a bug and mentioned it to Airtable support, but they haven’t addressed is per se.

P
  • Peter_Nelson
  • Author
  • Participating Frequently
  • March 25, 2019

The reason your formula is catching the supposedly-empty fullname fields is because the space makes it non-empty. You could modify the name-combination formulas like so, only combining the names if the first name actually exists:

IF({Racer Name (First)}, {Racer Name (First)} & " " & {Racer Name (Last)})

If there’s no first name, then the combo field would truly be blank, and it wouldn’t be picked up by your longer formula.


Justin, I thought of that and did try this formula using the first and last name fields instead of the calculated full name fields and it works as long as there is, in fact, a first name. Not so if there is a last name, without a first name - it then gets more complicated.

{Racer Name (First)} & " " & {Racer Name (Last)} &

IF({Racer 2 Name (First)}, ", " & {Racer 2 Name (First)} & " " & {Racer 2 Name (Last)}, {Racer 2 Name (Last)}) &
IF({Racer 3 Name (First)}, ", " & {Racer 3 Name (First)} & " " & {Racer 3 Name (Last)}, {Racer 3 Name (Last)}) &
IF({Racer 4 Name (First)}, ", " & {Racer 4 Name (First)} & " " & {Racer 4 Name (Last)}, {Racer 4 Name (Last)}) &
IF({Racer 5 Name (First)}, ", " & {Racer 5 Name (First)} & " " & {Racer 5 Name (Last)}, {Racer 5 Name (Last)})

It just seems like it should be A LOT EASIER to accomplish this. Whaddya say, Airtable?

Justin_Barrett
Forum|alt.badge.img+21

Justin, I thought of that and did try this formula using the first and last name fields instead of the calculated full name fields and it works as long as there is, in fact, a first name. Not so if there is a last name, without a first name - it then gets more complicated.

{Racer Name (First)} & " " & {Racer Name (Last)} &

IF({Racer 2 Name (First)}, ", " & {Racer 2 Name (First)} & " " & {Racer 2 Name (Last)}, {Racer 2 Name (Last)}) &
IF({Racer 3 Name (First)}, ", " & {Racer 3 Name (First)} & " " & {Racer 3 Name (Last)}, {Racer 3 Name (Last)}) &
IF({Racer 4 Name (First)}, ", " & {Racer 4 Name (First)} & " " & {Racer 4 Name (Last)}, {Racer 4 Name (Last)}) &
IF({Racer 5 Name (First)}, ", " & {Racer 5 Name (First)} & " " & {Racer 5 Name (Last)}, {Racer 5 Name (Last)})

It just seems like it should be A LOT EASIER to accomplish this. Whaddya say, Airtable?


In that case, I suggest keeping the first/last name combo fields, and design them like this (using the first as an example):

TRIM({Racer Name (First)} & " " & {Racer Name (Last)})

If no name is in either field, TRIM removes the lone space, leaving the field blank.
If only a first or last name exists, it removes the leading/trailing space.

That way your main function can be pretty trim (pun somewhat intended):

{Racer 1 Full Name} & IF({Racer 2 Full Name}, ", " & {Racer 2 Full Name})
    & IF({Racer 3 Full Name}, ", " & {Racer 3 Full Name})
    & IF({Racer 4 Full Name}, ", " & {Racer 4 Full Name})
    & IF({Racer 5 Full Name}, ", " & {Racer 5 Full Name})
D
Terms & ConditionsAccessibility statement