need Excel formula to break up first and last name

[sohunter]

I'm dealing with thousands or rows of names that need to be broken up to two columns: first (with middle initial sometimes) and last

Right now they're in one column. Some are like this
Jane Doe

some are like this
Doe, Jane

and then again, some are like this
Jane S. Doe

They need to end up like this:
First Last
Jane Doe
Jane S. Doe

Any advice on some formula shortcut for breaking them up (any of the cases above - just one would help) besides cut and pasting for hours, like I've been doing? I'm under serious deadline and it would take me, with my limited programming and formula writing skills, so long to figure it out. Thanks much if you can lend a hand.

 

[ETID]

highlite the column in question...from the menu Data>Text to Columns...follow the wizard.

 

[ETID]

Oh yeah...you might want to run an Edit>Replace (after the highlite and before the text to columns) to replace the commas with nothing

 

[bluedragon2]

This will get you the first part:

=IF(ISERROR(SEARCH(" ",SUBSTITUTE(A1," ","*",1))),LEFT(A1,FIND(" ",A1)-1),LEFT(A1,FIND(" ",SUBSTITUTE(A1," ","*",1))-1))

This will get you the last name:

=IF(ISERROR(SEARCH(" ",SUBSTITUTE(A1," ","*",1))),RIGHT(A1,LEN(A1)-FIND(" ",A1)),RIGHT(A1,LEN(A1)-FIND(" ",SUBSTITUTE(A1," ","*",1))))

But, you can also do a text to columns with a space delimiter and then massage your data.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[sohunter]

BTW...Thanks much to you both. I used ETID's suggestion since it was quicker for the situation at hand, but am saving bluedragon2's functions for future use as well.