Remove last character if its a comma

Sam_Ducker · ‎Aug 15, 2020

Hey so I have various different categories/tags linked together into a single string with a combination of rollups and a formula.

Sometimes there is a blank value for the last “{Tipologie Rollup}” and then I get an additional comma at the end of the string.

How do I add an if statement to say IF the last character of string = “,” remove it.

If it doesn’t do nothing.

My current formula which works.

IF({Technologie Rollup} = BLANK(), “”, {Technologie Rollup} & “,”) &
IF({Bisogni Rollup} = BLANK(), “”, {Bisogni Rollup} & “,”) &
IF({Settori Rollup} = BLANK(), “”, {Settori Rollup} & “,”) &
IF({Tipologie Rollup} = BLANK(), “”, {Tipologie Rollup})

Just need to add that cleanup if statement in the scenario there is no Tipologie Rollup data.

Justin_Barrett · ‎Aug 15, 2020

Welcome to the community, @Sam_Ducker! :grinning_face_with_big_eyes: There are two changes I suggest making.

First, you can simplify your field checking by using this format for each test:

IF({Field Name}, {Field Name} & ",")

By only inserting the field name into the first part of the IF() function, Airtable will read a non-empty field as equivalent to TRUE, and an empty field as equivalent to FALSE. Also, you can omit the third part of the IF() function if you want to return a blank value that matches the type of the other value returned in the second section. Long story short, if the field contains anything, it will be added. If not, nothing will appear.This will condense your code just a touch.

To piggyback on that and address your primary question, I suggest dropping the comma from the first tested field, and adding it to the front of each subsequent field (if it’s there). That turns the trailing comma into a leading comma, which will remove the need to do any cleanup.

With these two ideas combined, your formula becomes this:

IF({Technologie Rollup}, {Technologie Rollup}) &
IF({Bisogni Rollup}, "," & {Bisogni Rollup}) &
IF({Settori Rollup}, "," & {Settori Rollup}) &
IF({Tipologie Rollup}, "," & {Tipologie Rollup})

Sam_Ducker · ‎Aug 15, 2020

Justin_Barrett:

IF({Technologie Rollup}, {Technologie Rollup}) &
IF({Bisogni Rollup}, "," & {Bisogni Rollup}) &
IF({Settori Rollup}, "," & {Settori Rollup}) &
IF({Tipologie Rollup}, "," & {Tipologie Rollup})

Thanks for the help so far Justin, great advice on the formatting.

However the solution to the primary problem, just creates the reverse issue sadly,

now if the Technologie field is blank and the Bisogni has a value for example this creates a comma that I don’t want at the start of the string instead of the problem before where it would be at the end.

Don’t worry too much about the ID numbers specifically mean below but basically you can see how the row thats missing an ID number for Technologie creates the issue in the screenshots below. Which is a problem as when I then use this data in Zapier I have problems with validation because of the additional commas :frowning:

Justin_Barrett · ‎Aug 15, 2020

I was wondering if that might be the case. Most of the times that I’m doing something like this, there’s at least one field I’m pulling from that’s always there, so I usually put that first. If there’s a similar “always-there” field among these four, consider moving that to the front of the formula, and pushing {Technologie Rollup} later.

If that can’t be done, here’s one way to work around it. Instead of putting commas before everything right away, we’ll insert a block of spaces instead (I chose 5, but the number doesn’t matter as long as you’re consistent):

IF({Technologie Rollup}, {Technologie Rollup}) &
IF({Bisogni Rollup}, "     " & {Bisogni Rollup}) &
IF({Settori Rollup}, "     " & {Settori Rollup}) &
IF({Tipologie Rollup}, "     " & {Tipologie Rollup})

We can then wrap a TRIM() function around that, which will nix the leading spaces if {Technologie Rollup} happens to be missing, but leave the separating space blocks between the rest.

TRIM(
  IF({Technologie Rollup}, {Technologie Rollup}) &
  IF({Bisogni Rollup}, "     " & {Bisogni Rollup}) & 
  IF({Settori Rollup}, "     " & {Settori Rollup}) &
  IF({Tipologie Rollup}, "     " & {Tipologie Rollup})
)

Finally, we wrap SUBSTITUTE() around that to replace the space blocks with commas. Make sure you search for the same number of spaces that you added to the front of each field.

SUBSTITUTE(
  TRIM(
    IF({Technologie Rollup}, {Technologie Rollup}) &
    IF({Bisogni Rollup}, "     " & {Bisogni Rollup}) & 
    IF({Settori Rollup}, "     " & {Settori Rollup}) &
    IF({Tipologie Rollup}, "     " & {Tipologie Rollup})
  ), "     ", ","
)