Formula Help: Switching Middle and Last Names in Text String

System

This discussion was created from comments split from: How to convert Full name to Last name, First and middle Name(s)?.

Paul Mangan

So if we wanted to switch it where the middle name was with the last name how would we change the formula slightly? Tried a few variants and it's not working for me.

=IFERROR(RIGHT([Team Member]@row, LEN([Team Member]@row) - FIND("!", SUBSTITUTE([Team Member]@row, " ", "!", LEN([Team Member]@row) - LEN(SUBSTITUTE([Team Member]@row, " ", ""))))) + ", " + LEFT([Team Member]@row, FIND("!", SUBSTITUTE([Team Member]@row, " ", "!", LEN([Team Member]@row) - LEN(SUBSTITUTE([Team Member]@row, " ", "")))) - 1), "")

dojones

@Paul Mangan

Changing complicated formulas is sometimes a struggle. Here is a method that is more easily modifiable.

Create 3 columns and extract First Name, Middle Name, and Last Name. Then you can easily combine them in any order/format you want.

First Name:

=LEFT([Team Member]@row, FIND(" ", [Team Member]@row) - 1)

Middle Name:

=MID([Team Member]@row, FIND(" ", [Team Member]@row) + 1, FIND(" ", [Team Member]@row) + 1)

Last Name:

=RIGHT([Team Member]@row, LEN([Team Member]@row) - FIND(" ", [Team Member]@row, FIND(" ", [Team Member]@row) + 1))

Name:

=[Last Name]@row + " " + [Middle Name]@row + ", " + [First Name]@row

You can combine these to one formula but it becomes more complicated to build and change. When you work on a large formula, it is easier to build in pieces as above before combining to one formula.

Below is one formula to give the result.

=LEFT([Team Member]@row, FIND(" ", [Team Member]@row) - 1) + " " +

MID([Team Member]@row, FIND(" ", [Team Member]@row) + 1, FIND(" ", [Team Member]@row) + 1) + ", " +

LEFT([Team Member]@row, FIND(" ", [Team Member]@row) - 1)

@dojones

jmyzk_cloudsmart_jp

@Paul Mangan @dojones

I agree with adding helper columns to make the formula simple.

What @Paul Newcome 's original formula dealt with is the middle name with more than one word.

=SUBSTITUTE([Column A]@row, " ", "!", LEN([Column A]@row) - LEN(SUBSTITUTE([Column A]@row, " ", "")))

This formula replaces the last space with "!", making it easier to find the beginning of the last name where you have more than one word in the middle name.

https://app.smartsheet.com/b/publish?EQBCT=2bdcd871a14a4386aa9fdccc54449c19

Using Paul Newcome's method, a formula is as follows;

=MID([Column A]@row, FIND(" ", [Column A]@row + 1), FIND("!", SUBSTITUTE([Column A]@row, " ", "!", LEN([Column A]@row) - LEN(SUBSTITUTE([Column A]@row, " ", "")))) - FIND(" ", [Column A]@row)) + ", " + SUBSTITUTE([Column A]@row, MID([Column A]@row, FIND(" ", [Column A]@row + 1), FIND("!", SUBSTITUTE([Column A]@row, " ", "!", LEN([Column A]@row) - LEN(SUBSTITUTE([Column A]@row, " ", "")))) - FIND(" ", [Column A]@row)), "")

Step-by-Step Explanation:

1. Extracting the Middle Name:
   • MID([Column A]@row, FIND(" ", [Column A]@row) + 1, FIND("!", SUBSTITUTE([Column A]@row, " ", "!", LEN([Column A]@row) - LEN(SUBSTITUTE([Column A]@row, " ", "")))) - FIND(" ", [Column A]@row))
   • Explanation:
     • FIND(" ", [Column A]@row) + 1 locates the position of the first space after the first name, marking the start of the middle name.
     • FIND("!", SUBSTITUTE([Column A]@row, " ", "!", LEN([Column A]@row) - LEN(SUBSTITUTE([Column A]@row, " ", "")))) finds the position of the last space, which has been temporarily replaced with "!" using SUBSTITUTE.
     • The difference between the position of the last space and the first space gives the length of the middle name.
     • MID(...) then extracts the middle name based on this length.
2. Removing the Middle Name to Isolate First and Last Names:
   • SUBSTITUTE([Column A]@row, MID(...), "")
   • Explanation:
     • MID(...) from Step 1 extracts the middle name.
     • SUBSTITUTE([Column A]@row, MID(...), "") removes the extracted middle name from [Column A]@row, leaving only the first and last names as First Last.
3. Formatting the Result as Middle, First Last:
   • MID(...) + ", " + SUBSTITUTE(...)
   • Explanation:
     • This combines the extracted middle name, followed by ", ", and then the First Last structure to produce the desired format.