Hello!
I have tried every function I have found to switch the name order but I cannot figure out how to leave off the Suffix.
My cell has the text "Mr. Dave Smiths", I need to come up with a function to have it read "Smiths, Dave" in a separate column, leaving off the "Mr."
I have a long column I need to be able to copy the formula down through.
Any ideas?
Thank you!
Last edited by mattsgirl614; 12-07-2014 at 01:19 AM.
Use "Text to columns" to disintegrate words using space as separator and then use "Concatenate" formula to stitch words as you want.
Try this formula in B1
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",25)),25))&", "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",25)),25,25))
Row\Col Mr. Dave Smiths Smiths, Dave
Last edited by AlKey; 12-07-2014 at 01:48 AM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
That worked perfect on the first cell, but when I tried to copy it down the column, the other first names had more letters, and they would only show the first four letters of their first names, I'm not sure what I'm doing wrong :/ Thank you for your help!
Originally Posted by AlKey
Sorry, try updated formula in my original post.
That worked perfect, thank you so much!
Another tough one..you don't want to get rid of names, but MsRsmith might get butchered so...I would suggest a search an repllace of all the common suffixes first (Ms.,Mr., Messr,...basically any common suffixes and variations, (this can use the program's tools or; the various searh and replace formulas available on THIS site) then you nee to clean up any missed variations of such...this will not be easy as everyone inputs those differentlt (some May capitalize or not, some may put a "." or not; etc....)... it is not an easy task, especially without using VBA...
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
You're welcome and thank you for the feedback!
Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools -> Mark thread as Solved).
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks