Help

Re: Conditional Concatenate (First Name, Middle Initial, Last Name)

Solved
Jump to Solution
1936 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Nick_Kerzman
5 - Automation Enthusiast
5 - Automation Enthusiast

Can I please get some assistance with a conditional statement. I’ve looked at examples, but have not been able to crack this.

Goal: First Name space Middle Initial space Last Name or First Name space Last Name
Not all names have a middle initial therefore my current formula is creating a double space when no middle initial present. I know this will require some kind of conditional if statement. There may also be a more graceful way of handling the spaces between field values.

Current Formula
CONCATENATE({First Name}," ",{Middle Initial}," ",{Last Name})

Data Structure

First Name Middle Initial Last Name
Todd Roberts
Rene M. Johnson
1 Solution

Accepted Solutions
Nick_Kerzman
5 - Automation Enthusiast
5 - Automation Enthusiast

@Aron, thank you for the update and the logic statement. It helped me debug two things.

A space was needed between the middle set of quotes to have a space between the first and last name when no middle initial was present. Second, the quotes around the second Middle Initial reference needed to be updated as the curly quotes were causing an invalid issue.

This was the final formula, like yours with the two minor edits.
CONCATENATE({First Name},IF({Middle Initial}=""," "," "&{Middle Initial}&" "),{Last Name})

Much appreciated for the learning experience.

See Solution in Thread

4 Replies 4
Aron
7 - App Architect
7 - App Architect

You can add an If in that concatenate

CONCATENATE({First Name},if({Middle Initial}="",""," “&{Middle Initial}&” "),{Last Name})

Nick_Kerzman
5 - Automation Enthusiast
5 - Automation Enthusiast

Aron, thanks for the assistance.

The formula is returning as an invalid formula. Could you check, and would you mind also sharing the “logic” statement so I can get better at writing these in the future?

Such as:
Concatenate {First Name}, if {Middle Intial} IS NULL…

For example, what does the ==, the ampersands and some of the other notation used to achieve the conditions? Thanks!

The == were a typo :slightly_smiling_face:

The logic statement is that if formula in the middle, it check whether the middle initial is empty. If it is, it returns “”, if not it returns the middle initial and adds a space on each side of it.

I’ll let you debug the formula with your variable names!

Nick_Kerzman
5 - Automation Enthusiast
5 - Automation Enthusiast

@Aron, thank you for the update and the logic statement. It helped me debug two things.

A space was needed between the middle set of quotes to have a space between the first and last name when no middle initial was present. Second, the quotes around the second Middle Initial reference needed to be updated as the curly quotes were causing an invalid issue.

This was the final formula, like yours with the two minor edits.
CONCATENATE({First Name},IF({Middle Initial}=""," "," "&{Middle Initial}&" "),{Last Name})

Much appreciated for the learning experience.