#### gquest

##### Board Regular
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.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Norie

##### Well-known Member
Why are you searching for a space when it appears the delimiter is actually a comma?

#### brian.wethington

##### Well-known Member
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

##### Board Regular

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

#### gquest

##### Board Regular
Thanks Todd!

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,625
Messages
5,838,427
Members
430,547
Latest member
jopshio

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back