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

Ask the community Formulas
#1

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?

Capture
Capture611×578 23.6 KB

#2

Hi Patrick, Try this;

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

2 Likes
#3

Thank you very much! Worked perfectly :slight_smile:

Thank you.

1 Like
#4

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

#5

I figured this out! Thanks :slightly_smiling_face:

#6

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}
    )
  )
)
#7

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))

Concatenate fields with comma, except when fields are empty
#8

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.

7 Likes
#9

Thanks a million!! This worked perfectly :grinning:

1 Like
#10

@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.

#11

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.

1 Like
#12

Amazing! Thank you! worked perfectly.

1 Like