Hi Patrick, Try this;

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

Thank you very much! Worked perfectly

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

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

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