Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2008
    Location
    Wayne, Pennsylvania, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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))
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    New Lounger
    Join Date
    Sep 2008
    Location
    Wayne, Pennsylvania, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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. #5
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

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