IF/CONCATENATE Help

[Dakota_Bushnell]

Hello,

I’m currently trying to take my primary field in a table and concatenate based on what applies.

The table itself is pretty basic - using it for client information; i.e. First Name, Middle Initial, Last Name, Spouse First Name, Spouse Last Name, and other basic information.

We have some clients where we want the spouses other last name for whatever reasons.

What I’m wanting to do is have the primary field concatenate certain information when 1 IF applies, OR concatenate different information if the OTHER IF applies.

For Example: IF({Spouse Last Name}!=Blank(), Concatenate({Last Name}, ", ", {First Name}, " & ", {Spouse First Name})) -OR- IF({Spouse Last Name}, Concatenate({Last Name}, ", ", {First Name}, " & ", {Spouse Last Name}, ", ", {Spouse First Name})

My problem is combining those IF statements so that both would apply, so IF it meets criteria of 1, it applies that concatenated formula, or if it meets the criteria of the other 1, it applies that concatenated formula instead.

Please help!

[Jeremy_Oglesby]

Hi @Dakota_Bushnell,

I think this should get you what you are looking for, and it simplifies the logic a little:

{Last Name} &
", " &
{First Name} &
IF(
    OR(
        {Spouse First Name},
        {Spouse Last Name}
    ),
    " & "
) &
IF(
    {Spouse Last Name},
    {Spouse Last Name} & ", "
) &
{Spouse First Name}

There are certain elements that you will always want to output, so leaving those elements outside of the conditional statements makes the conditional statements themselves cleaner. You just concatenate the constant elements with your conditional elements using the “&” concatenation operator.

I also added a text ampersand “&” in between the main person’s name and the spouse’s name for better formatting (to my mind at least) – you can change that if desired.

This will output the person’s name, formatted LAST, FIRST; next, it will check to see if a spouse has been listed (checking for the presence of either a last name or a first name), and if found, it prints the ampersand to add the spouse’s name after the main person’s name. Then, it checks if there is a spouse last name, and if so, prints it with a tailing comma. Lastly, it always prints the spouse’s first name (which will print blank if it’s not there).

So output examples would look like this:

Smith, John

Smith, John & Jane

Smith, John & Hunter-Smith, Jane

[Dakota_Bushnell]

Jeremy!

Thank you for your help. This works for the most part, however the only problem I have now is when the last name of the Spouse is the same as the “Last Name” field.

How would I get it to be able to see that and react accordingly?

See the image below for reference.

I want it to be able to see that {Spouse Last Name}={Last Name} and then populate w/ {Last Name}, {First Name} & {Spouse First Name}.

Any assistance, guidance or suggestions are appreciated!

[Justin_Barrett]

That would only require a small modification to @Jeremy_Oglesby’s original formula:

{Last Name} &
", " &
{First Name} &
IF(
    OR(
        {Spouse First Name},
        {Spouse Last Name}
    ),
    " & "
) &
IF(
    AND(
        {Spouse Last Name},
        {Spouse Last Name} != {Last Name}
    ),
    {Spouse Last Name} & ", "
) &
{Spouse First Name}

[Dakota_Bushnell]

Justin!

Thank you so much! I’ve been giving myself a headache looking at airtable formulas all day and not being able to make even minor tweaks without breaking my formulas. Haha.

Thanks again!

[Justin_Barrett]

I appreciate the “Solution” nod, but credit really should go to @Jeremy_Oglesby for making the original.

[Dakota_Bushnell]

Perhaps you could help me with another addition to this formula?

Let’s say I have 2 more columns: {Middle Initial} and {Spouse Middle Initial}.

I’m trying to get it to appear as "{Last Name} & “, " & {First Name} & " " & {Middle Initial} & “.” if it’s an individual.”

For a spouse with the same last name: "{Last Name} & ", " & {First Name} & " " & {Middle Initial} & “.” & " & " & {Spouse First Name} & " " & {Spouse Middle Initial} & “.”

For a spouse with a different last name: "{Last Name} & ", " & {First Name} & " " & {Middle Initial} & ". "{Spouse Last Name}, {Spouse First Name} {Spouse Middle Initial}.

I’ve been playing with this formula trying to input it in here some where for the middle initials and I can’t figure it out. I can get a working formula, but if the last name is the same, it doesn’t even populate the spouse name at all and leaves the “&”.

[Jeremy_Oglesby · Accepted solution]

{Last Name} &
", " &
{First Name} &
IF(
   {Middle Initial},
   " " & {Middle Initial} & "."
) &
IF(
    OR(
        {Spouse First Name},
        {Spouse Last Name}
    ),
    " & "
) &
IF(
    AND(
        {Spouse Last Name},
        {Spouse Last Name} != {Last Name}
    ),
    {Spouse Last Name} & ", "
) &
{Spouse First Name} &
IF(
   {Spouse Middle Initial},
   " " & {Spouse Middle Initial} & "."
)

[Mitchell_Findle]

I’m working on something similar, but with a normal layout without the commas and “last name, first name” structure. I can’t quite figure out how to get this same formula to work when I want the output to be “Barack and Michelle Obama”. Any help would be great!

[Justin_Barrett]

@Mitchell_Findley1 Sorry that your message wasn’t addressed before now. Were you able to get it working, or do you still need help?