Concatenate Name if Preferred Name not blank

okm

Hi, I am trying to create a formula where I can concatenate a person's name as "Smith, John" if they don't have a preferred name listed (if blank) and as "Smith, Jane (John)" if they do have a preferred name.

My fields are Last Name, First Name, and Preferred Name.

Can anyone help? Thanks so much!!!

kchenoweth

To make this work you will need to add a column in between your First Name and Preferred Name columns. Then use this as a column formula for the newly added column:

=JOIN([Last Name]@row:[First Name]@row,", ")

Then in your desired result column use this formula:

=IF([Preferred Name]@row="",[New Column]@row,JOIN([New Column]@row:[Preferred Name]@row," "))

okm

That helps with making both names appear, but I need it all in one column and with the preferred name before the legal name. Thank you!

kchenoweth

It should look something like this.

Paul Newcome

You should be able to do it without a helper column.

=[Last Name]@row + ", " + [First Name]@row + IF([Preferred Name]@row <> "", "(" + [Preferred Name]@row + ")", "")