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

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!
Best Answer
-
Thank you! It works like a charm.
I never would have figured that out on my own.
Answers
-
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)
-
Thank you! It works like a charm.
I never would have figured that out on my own.
-
Help Article Resources
Categories
Check out the Formula Handbook template!