Find / Left Function for Names... Please Help

gquest

Board Regular
Joined
Feb 20, 2007
Messages
167
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.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,263
Office Version
  1. 365
Platform
  1. Windows
Why are you searching for a space when it appears the delimiter is actually a comma?
 

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
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
Joined
Feb 20, 2007
Messages
167

ADVERTISEMENT

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
 

Watch MrExcel Video

Forum statistics

Threads
1,126,957
Messages
5,621,823
Members
415,859
Latest member
Vain

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top