Excel formula help

K

Kelly

Hi~
Does anyone know how to change a text string like this...
Joe M. Smith
to this...
Smith, Joe M.
or vice versa (Smith, Joe M. to Joe M. Smith)

I can't figure it out!! It would save me retyping 800
names!!! Thank you for any help anyone can provide!!!

Thank you!!
 
D

David McRitchie

B

Big Chris

See attached....it's easy to do it bit by bit.

This method means inserting 4 columns, entering the fomulae and feeding
down then pasting values over the final column.

Any variable content of the text within the original cell may throw
this out as I've set it to look specifically for a period, but you can
alter it to find spaces etc.

File Attached: http://www.exceltip.com/forum/attachment.php?postid=298962 (excel formula help.xls)
 
R

Ron Rosenfeld

Hi~
Does anyone know how to change a text string like this...
Joe M. Smith
to this...
Smith, Joe M.
Click to expand...

=RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-
LEN(SUBSTITUTE(A1," ","")))))&", "&LEFT(A1,LEN(A1)-FIND("~",
SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
or vice versa (Smith, Joe M. to Joe M. Smith)
Click to expand...

=RIGHT(B1,LEN(B1)-FIND(",",B1)-1)&" "& LEFT(B1,FIND(",",B1)-1)


--ron
 
L

llinard

Wow...I JUST had to do this yesterday!
In my case, I wanted to change "Smith, Joe" (in Col A) to "Joe Smith"
I created a new col B and pasted the names from Col A into it.
Then I created a new col C and did the same thing.
So now I had 3 cols with the exact same info in them.
In col B I did a find & replace for ",*" with nothing -- so thi
deleted the comma and everything after it.
In col C I did a find & replace for "*," with nothing -- this delete
the comma and everything *before* it.
Then in colD I used CONCATENATE in to join Col B + a space + Col C.
Then I copied Col D & did a "paste special: values" into col E (so
could delete the other columns without getting a #REF error).
(FYI, I left col A in case I screwed up and needed to start over. Onc
I had the finished values in Col E, I deleted cols A-D.)

Doing it on names without a comma -- like Joe Smith -- would b
trickier. Maybe you could find & replace " *" (space *) but then name
with two spaces like Mary Ann Baker would be screwed up. Hmmmm.

Give it a try and let me know how it works! Good luck
 
Top