Find / Left Function for Names... Please Help

[gquest]

Hello everyone. I'm trying to seperate last names and first names from one single cell into two different cells, but I'm having issues with surnames and two word last names. To complicate matters further the surnames are in different formats. Here are a few examples of the orginal names (last name, First Name):

Hammen, Jr., Ed
Facend Jr., Terry
Flynn, Mel
Van Fleet, Jean
Crowe, Don

I am using the following formula to get the first name:

=IF(ISERROR(RIGHT(R2,LEN(R2)-FIND(" ",R2,1))),"",RIGHT(R2,LEN(R2)-FIND(" ",R2,1)))

And the following formula to get the last name:

=IF(ISERROR(LEFT(R2,(FIND(" ",R2,1)-2))),"",LEFT(R2,(FIND(" ",R2,1)-2)))


How can I adjust my formulas to account for the two surnames in different formulas and the two word last names? These formulas work fine for single word first names and single word last names. I want to put the last name followed by surname in one cell and the first name in another.

Thank you in advance for your help.

 

[Norie]

Why are you searching for a space when it appears the delimiter is actually a comma?

 

[ADAMC]

Does this link help:

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

 

[brian.wethington]

Best to not use a formula at all....


You could use text to column:

Make sure you have a few empty columns to the right of the column you will be doing this in first.
Then select the column you wish to seperate
Choose the data menu, text to columns...
Choose Delimited, and next
Mark only the comma, and click finished

Now you will have seperated at any comma in the cell unfortunately this may give you 3 columns but for the most part it should be done mostly correct and you can fix the others quickly by sight.

Hope this helps!

 

[gquest]

Why are you searching for a space when it appears the delimiter is actually a comma?

Wow, I can't believe I missed that... Thanks for the input. Can u think of a way for the formula to fix the two comma one (see below)?

Hammen, Jr., Edward

 

[ExcelChampion]

Perhaps:

Book1
	A	B	C	D
1	Hammen,Jr.,Ed		Hammen,Jr.	Ed
2	FacendJr.,Terry		FacendJr.	Terry
3	Flynn,Mel		Flynn	Mel
4	VanFleet,Jean		VanFleet	Jean
5	Crowe,Don		Crowe	Don
Sheet1

Why are you searching for a space when it appears the delimiter is actually a comma?

Not really because there is a comma after "Hammen" that would throw that off.

 

[gquest]

Thanks Todd!