Evening,
I have a list of names in the following format: Last, First Middle. What I need is a formula that will remove the middle name, and re-arrange the format to First Last with no comma separating the names. Currently the entire name is contained in a single cell and I would like it to remain that way.
I have:
Gates, Bill Henry
I want:
Bill Gates
Thank you for your assistance.
One way (data in A1)
=TRIM(MID(SUBSTITUTE(" "&TRIM(SUBSTITUTE($A1,",",""))," ",REPT(" ",125)),125*COLUMNS($A:B),125)&" "&TRIM(MID(SUBSTITUTE(" "&TRIM(SUBSTITUTE($A1,",",""))," ",REPT(" ",125)),125*COLUMNS($A:A),125)))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hi,
A shorter version might be
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,",","")," ",REPT(" ",100)),100,100)&" "&TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A1,",","")," ",REPT(" ",100)),100)))
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Thank you both. Anytime I think I'm semi good with Excel, I'm reminded just how much I need to learn when I see formulas like the above. If either of you would like to expand on how the formula works feel free to. I will leave the post open a bit longer before marking as solved in case anyone wants to add anything to the thread. Thx again.
SUBSTITUTE($A1,",","") removes the comma, to give Gates Bill Henry.
Then two bits like this:
TRIM(MID(SUBSTITUTE(" "&$B1," ",REPT(" ",125)),125*COLUMNS($A:A),125))
puts a space in front of Gates Bill Henry) and then substitutes 125 spaces for each space in " Gates Bill Henry". The MID part then starts at position 125*COLUMNS($A:A) (125*1=125) and returns the next 125 characters ("Gates" and a load of spaces). Trim removes the. Othe other bit
TRIM(MID(SUBSTITUTE(" "&$B1," ",REPT(" ",125)),125*COLUMNS($A:b),125))
does teh same, starting at position 125*COLUMNS($A:b) being 125*2=250, etc....
Join em up and there's your answer...
Another way:
=LEFT(TRIM(MID(A1,SEARCH(",",A1)+1,255)),SEARCH(" ",TRIM(MID(A1,SEARCH(",",A1)+1,255)))-1)&" "&LEFT(A1,SEARCH(",",A1)-1)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks