How to convert Full name to Last name, First and middle Name(s)?

MarianneD617

I have a column with Full names (some with extended middle names) and want to create a column for Last Name, First and Middle name(s).

For example, convert Column A "Angela Marie Walters Smith" to value in Column B of "Smith, Angela Marie Walters"

What formula will return the value I'm looking for?

Thank you!

Paul Newcome

Try this:

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

MarianneD617

Thank you! It works like a charm.

I never would have figured that out on my own.

Paul Newcome

Try this:

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

MarianneD617

Thank you! It works like a charm.

I never would have figured that out on my own.

Paul Newcome

Happy to help. 👍️