Concatenate formaula, adding a comma, but if fields are empty still adds comma

[Patrick813]

Hi, Rookie question;

I have 3 columns “Full Name” (which has the formula to join the next two columns) “Last name” and “First Name”, putting a comma in between.

The formula I’m using is: CONCATENATE({Last name}&", "&{First name})

The issue is when Last name and First name are blank (empty records), the formula runs indefinitely in the Full Name column creating rows and only putting in the comma (see pic). How do I stop this?

[kuovonne]

Because you will always have a title, you can have a much simpler formula without all the AND checks.

CONCATENATE(
    {Title},
    IF({Last Name (from Composer)}, ", " & {Last Name (from Composer)}),
    IF({Last Name (from Arranger)}, ", arr. " & {Last Name (from Arranger)}),
    IF({Voicing}, ", " & {Voicing})
)

My previous post was from two years ago, and I now have a cleaner style for writing these types of formulas.

[afs]

Amazing! Thank you! worked perfectly.

[Angelena_Zeiser]

Hello! Newbie here.

My formula:
CONCATENATE({First Name}," ",{Last Name}, " - ", Officer)

But if the Officer field is blank, then I don’t want the " - ", just first and last name.

Thank you!

[TheTimeSavingCo]

Hi Angela, try this:

IF(
  Officer,
  {First Name} & " " & {Last Name} & " - " & Officer,
  {First Name} & " " & {Last Name}
)

[Angelena_Zeiser]

Angelena_Zeiser:

CONCATENATE({First Name}," ",{Last Name}, " - ", Officer)

Thank you Adam C! It worked!

[Paul_Wallace]

Hi - i am trying to understand how to read these properly. I have the same issue, where i want to Concatenate text with a comma and space ", " from lookup fields but i do not want to add this comma and space if the field is blank. I have multiple lookup fields

How do i learn this function. I stare at the coding and formula trying to figure it out but it is like looking at another language

[jon9clark1]

I am having a similar issue. I'm having commas show up when I don't want them to show up if the fields are empty.

I'm trying to use this formula which randomly used to work:

CONCATENATE({Address 1} & "\n" & IF({Address 2}, {Address 2} & "\n") & {Address 3}," , ",{State / County}," ",{Zip / Postal Code})

I want it so that if there isn't an address then no comma shows up (leaving just a blank field)