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 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.
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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 208 Use Cases
- 517 Announcements
- 5.6K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 84 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!