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

Hi Patrick, Try this;

=IF(OR(FIRSTNAME=BLANK(),LASTNAME=BLANK()),CONCATENATE(LASTNAME,FIRSTNAME),CONCATENATE(LASTNAME,", ",FIRSTNAME))

Thank you very much! Worked perfectly :slightly_smiling_face:

Thank you.

Hi,
Can I ask another question?
I have two columns, 1st is “Days since contact” I want the 2nd column to be a formula for IF 0-30 = HOT, 30-60 = WARM, >60 = COLD

What’s the proper formula/syntax for this?

Thanks, Pat

I figured this out! Thanks :slightly_smiling_face:

How about this:

  IF({Days since contact} > 60,
    "COLD",
    IF({Days since contact} > 30,
    "WARM",
    "HOT"
    )
  )

If you want to use the actual date of the last contact in the formula:

IF({Date},
  IF(DATETIME_DIFF(TODAY(), {Date}, 'd')  > 60,
    "COLD",
    IF(DATETIME_DIFF(TODAY(), {Date}, 'd')  > 30,
    "WARM",
    "HOT"
    )
  )
)

By the way, here is a slightly different formula for your original question. It will put a “?” as a placeholder for the missing name if only the first or last name is given.

IF(AND({Last name}, {First name}),
  {Last name} & ", " & {First name},
  IF({Last name},
    {Last name} & ", ?",
    IF({First name},
      "?, " & {First name}
    )
  )
)

Hello, I’m having the same issue and found this formula to work, but I can’t figure out how to extend the formula to concatenate 4 columns instead of 2. I tried using the below, but that didn’t work. I’m sorry to be so daft, but could you show an example of this formula set up for 4 columns, please? Thank you!!!

IF(OR(Building=BLANK(),City=BLANK(),State=BLANK(),Country=BLANK()),CONCATENATE(Building,City,State,Country),CONCATENATE(Building,", “,City,”, “,State,”, ",Country))

I recommend using the & operator instead of the CONCATENATE function. It does the same thing but is a bit clearer.

You also need to think through the logic when there should be a comma and space.

Building & IF(AND(Building, OR(City, State, Country)), ", ")
& City & IF(AND(City, OR(State, Country)), ", ")
& State & IF(AND(State, Country), ", ")
& Country

It would be much simpler if the ARRAYJOIN function worked with user-entered arrays, but it only works with linked record fields, rollups, and lookups.

Building & IF(AND(Building, OR(City, State, Country)), ", ")
& City & IF(AND(City, OR(State, Country)), ", ")
& State & IF(AND(State, Country), ", ")
& Country

Thanks a million!! This worked perfectly :grinning:

@kuovonne I found your recommendation worked for trying to combine music title with composer and arranger and voicing. Thank you!
Title & IF(AND(Title, OR(Composer, Arranger, Voicing)), ", “)
& {Last Name (from Composer)} & IF(AND(Composer, OR(Arranger, Voicing)),”, ")
& {Last Name (from Arranger)} & IF(AND(Arranger, Voicing), ", ")
& Voicing

Is there a way to add text to the formula so if there is an arranger it lists “arr.” before their name?
i.e. Hallelujah Chorus, Handel, arr. Shaw, SATB
When I try to add it at the end of the second line with the comma, it includes the “arr.” if there isn’t an arranger but there is a voicing.

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.

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

Amazing! Thank you! worked perfectly.

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!

Hi Angela, try this:

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

Thank you Adam C! It worked!

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

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: