Skip to main content
Solved

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

  • April 21, 2020
  • 4 replies
  • 103 views
N

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

Best answer by Nick_Kerzman

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!


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

4 replies

A
Forum|alt.badge.img+3
  • Aron11
  • Inspiring
  • April 21, 2020

You can add an If in that concatenate

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

A
N
  • Nick_Kerzman
  • Author
  • New Participant
  • April 21, 2020

You can add an If in that concatenate

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


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!

A
Forum|alt.badge.img+3
  • Aron11
  • Inspiring
  • April 21, 2020

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!

N
  • Nick_Kerzman
  • Author
  • New Participant
  • Answer
  • April 21, 2020

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!


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

A
Terms & ConditionsAccessibility statement