Add comma after first word only

[sunshine0322]

I have an excel list of about 300+ names. Each name is in the same cell written as "LAST NAME FIRST NAME MIDDLE INITIAL" with no commas. (Ex. Doe Jane A). I need a way to insert a comma ONLY after the first word in the cell so it will read "Doe, Jane A". Is there any way to do this without having to do this all manually?

Thanks!

 

[Richard Schollar]

Hi

=REPLACE(A1,FIND(" ",A1),0,",")

copied down in an adjacent column and then copy+paste special> values over the originals.

 

[Sandeep Warrier]

Hi & welcome to the Board!

Try

Excel Workbook
	A	B
1	Doe Jane A	Doe, Jane A
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B1	=REPLACE(A1,FIND(" ",A1),0,",")

 

[jlatendre]

Hello,
=REPLACE(A1,FIND(" ",A1,1),1,", ")
Jesse

 

[sunshine0322]

And it works!! WHOOT! WHOOT!!! Thanks! This is fantastic! Saved me so much time!

 

[sunshine0322]

Okay now my worksheet looks like this

Excel Workbook
	A	B
1	Doe Jane A	Doe, Jane A
Sheet1

Is there any way to now delete column A without messing up my formula for column B? Each time I try to delete column A now, it causes column B to say "REF#".

 

[jlatendre]

Why not just hide column A? Or you may also copy and paste special: values.
Jesse

 

[sunshine0322]

Perfect! Worked like a charm. Phew! Thanks again for the help!