# Thread: Splitting Full Name in 1 column into First, Middle, Last

1. Good morning -

I think for one of you pros this is an easy task, but I'm no pro, so here goes.

I have a spreadsheet that has names that are imported as full names, with last name first.

i.e. DOE, JOHN JAMES

I need to split the names into separate columns for First, Middle, Last. Each name is potentially a different length (up to a point). Any help would be greatly appreciated.

Thanks!

2. You could do the following:
• Make sure that there are two blank columns to the right of the column with names.
• Select the column with names.
• Select Data | Text to Columns... to call the Convert Text to Columns Wizard.
• Select Delimited
• Click Next.
• Tick the check boxes for Comma and Space.
• Click Finish.

If things go wrong, you can use Undo to restore the original situation.

3. [quote name='Annita' post='765788' date='17-Mar-2009 21:16']Good morning -

I think for one of you pros this is an easy task, but I'm no pro, so here goes.

I have a spreadsheet that has names that are imported as full names, with last name first.

i.e. DOE, JOHN JAMES

I need to split the names into separate columns for First, Middle, Last. Each name is potentially a different length (up to a point). Any help would be greatly appreciated.

Thanks![/quote]

One way with formula
Assuming that there is always a space before the first name and a comma follow by a space
after the last name but before the middle name

try the formula in the screenshot, adjust the range to suit yours.

B8 '=LEFT(A8,FIND(",",A8)-1)
C8 '=MID(A8,FIND(" ",A8)+1,FIND(" ",A8,FIND(" ",A8)+1)-1-FIND(" ",A8))
D8 '=RIGHT(A8,LEN(A8)-FIND(" ",A8,FIND(" ",A8)+1))

4. [quote name='franciz' post='765802' date='17-Mar-2009 15:12']One way with formula
Assuming that there is always a space before the first name and a comma follow by a space
after the last name but before the middle name

try the formula in the screenshot, adjust the range to suit yours.

B8 '=LEFT(A8,FIND(",",A8)-1)
C8 '=MID(A8,FIND(" ",A8)+1,FIND(" ",A8,FIND(" ",A8)+1)-1-FIND(" ",A8))
D8 '=RIGHT(A8,LEN(A8)-FIND(" ",A8,FIND(" ",A8)+1))[/quote]

The first part worked fine for the last name, but the other 2 return invalid values when there is no middle name . . . and if there happens to be a space in the first name (MC CONNELL), I get no last name and the first name shows as CONNELL, - for what I need I think the text to columns option that Hans suggested will work fine. Thanks for your help!

Annita

5. [quote name='Annita' post='765817' date='17-Mar-2009 23:52']The first part worked fine for the last name, but the other 2 return invalid values when there is no middle name . . . and if there happens to be a space in the first name (MC CONNELL), I get no last name and the first name shows as CONNELL, - for what I need I think the text to columns option that Hans suggested will work fine. Thanks for your help!

Annita[/quote]

Thanks for the feedback.
These formulas assume that the the names to parse always are the same format provided in your example,
if its varies, Hans's solution will be a better option.
Glad that there's a solution works for you.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•