LinkBack URL
About LinkBacks
I have a list of names that are currently in the format:-
LASTNAME MIDDLENAME FIRSTNAME
I want to change this to:-
Firstname Lastname
The attached spreadsheet should clarify excatly what I'm trying to do.
I have a list of names that are currently in the format:-
LASTNAME MIDDLENAME FIRSTNAME
I want to change this to:-
Firstname Lastname
The attached spreadsheet should clarify excatly what I'm trying to do.
In B1:
=PROPER(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),50))&" "&LEFT(A1,FIND(" ",A1)-1))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Try this and drag down
Please Login or Register to view this content.
Happy with my advice? Click on the * reputation button below
Perfect - many thanks
Whom are you addressing?
Sorry - I was addressing you AliGW.Originally Posted by AliGW
Whom are you addressing?
My list of names is actually in different name formats. Please do you know the formula adjutments if the name is LASTNAME FIRSTNAME MIDDLENAME (as per attached)
Oh, here we go ...
Before I look, is there any other format not included that I need to know about?
It's yet another case of an over-simplified data sample. Please don't do that.
No it was an oversight on my part, the majority of my dataset is LASTNAME FIRSTNAME MIDDLENAME
The example data wasn't over simplified, it was just I didn't check my list thoroughly enough to establish the correct format.
Last edited by AliGW; 07-26-2019 at 07:23 AM. Reason: Please don't quote unnecessarily!
OK - here you go:
=PROPER(TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(" ",A1)+1,50)," ",REPT(" ",50)),50))&" "&LEFT(A1,FIND(" ",A1)-1))
If you have a mix of the two formats, that's tough, I'm afraid, as Excel will not be able to distinguish between what is a first, second and last name except by position.
Yea my dataset is far from ideal but unfortunately I don't control that. I will just make manual adjustments as and when necessary.
This is perfect - Many thanks (again).
Last edited by AliGW; 07-26-2019 at 07:52 AM. Reason: Please don't quote unnecessarily!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks