Excel formula help

[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!!

 

[J.E. McGimpsey]

Take a look here:

http://cpearson.com/excel/FirstLast.htm

 

[David McRitchie]

Hi Kelly,
I'm sure Chip's page covers changing in both directions,
as I think that was the primary intent of the page.

But you also take a look at SepLastTerm in
http://www.mvps.org/dmcritchie/excel/join.htm#seplastterm
so you have first and middle names in one cell, and lastname in one cell.

If after collecting your data, you find you need to change the
capitalizations to proper for most columns and upper for zip state codes
http://www.mvps.org/dmcritchie/excel/proper.htm

 

[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)

 

[Ron Rosenfeld]

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

=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)

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


--ron

 

[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