Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Apr 21, 2020 05:48 AM
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 |
Solved! Go to Solution.
Apr 21, 2020 07:06 AM
@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.
Apr 21, 2020 05:58 AM
You can add an If in that concatenate
CONCATENATE({First Name},if({Middle Initial}="",""," “&{Middle Initial}&” "),{Last Name})
Apr 21, 2020 06:19 AM
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!
Apr 21, 2020 06:57 AM
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!
Apr 21, 2020 07:06 AM
@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.