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.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!