Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Full Name Column to To Two Columns (2003)

    Could someone please point me to a script that I seem to remember seeing quite awhile ago but can't find now, where running the script will take a column of full names like this:

    Mary Zarrelli
    James C. Wood
    Wendy M. Smith
    John Smith Jr.
    Kyle Zimmer II

    and break it into two columns named "First Name" and "Last Name" without keeping the middle initials but keeping the suffix to last name like this:

    FirstName Column
    Mary
    James
    Wendy
    John
    Kyle

    LastName Column
    Zarrelli
    Wood
    Smith
    Smith Jr.
    Zimmer II

    Would very much appreciate any guidance you can give me. Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Full Name Column to To Two Columns (2003)

    Although there have been several threads about splitting names into parts, I couldn't find a thread dealing with this particular situation. I have attached a solution using formulas; the formula for fist name is very simple, but that for last names is complicated (I built it using many intermediate steps, then removed the intermediate steps).
    If you would prefer a macro solution, post back.
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Full Name Column to To Two Columns (2003)

    Wow! Hans, thank you so much! The formulas are great! I'm wondering, though, do you think having an underlying formula in the fields might interfere with software programs that are to run on the worksheet and reference the various fields? I guess I could remove the formulas when I'm finished, though, right?

    If it's not too much trouble, and you already have a macro to accomplish the same thing, I'd love to have it, too, and if middle initials are a problem, perhaps I could search for "space, any alphabetic character, period, space," and replace with nothing. What do you think? Many thanks, Hans!...Mary

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Full Name Column to To Two Columns (2003)

    You can copy the columns with first name and last name, then paste special with the values option. This removes the formulas.
    I'll post a macro solution later - have to do some shopping for dinner first. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Full Name Column to To Two Columns (2003)

    Many thanks, Hans! I had almost forgotten how to remove formulas and leave values. Appreciate your quick attention to my needs! Until next time...Mary

  6. #6
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Full Name Column to To Two Columns (2003)

    To me, the magic bit of this is that if you add a name into col A, cols B & C split it into firstname and lastname even though at the point of entering the new name there are no formulas in cols B & C of that record.
    What's the trick to do that, Hans?
    I know Data Validation contains that option, but what's your trick?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Full Name Column to To Two Columns (2003)

    Here is a macro you can use. Select the cells with full names before running it:
    <code>
    Sub SplitNames()
    Dim oCell As Range
    Dim intPos As Integer

    ' Optional: insert cells to the right of the selection
    Selection.Offset(0, 1).Resize(ColumnSize:=2).Insert Shift:=xlShiftToRight

    ' Remove middle initial
    Selection.Replace What:=" ?. ", Replacement:=" ", LookAt:=xlPart

    ' Loop through cells
    For Each oCell In Selection.Cells
    ' Position of space
    intPos = InStr(oCell, " ")
    ' First name
    oCell.Offset(0, 1) = Left(oCell, intPos - 1)
    ' Last name
    oCell.Offset(0, 2) = Mid(oCell, intPos + 1)
    Next oCell
    End Sub
    </code>
    The line to insert cells to the right of the selection is optional; you can comment it out or remove it if you don't need it.
    I used your idea to remove the middle initials before splitting the name - that makes it a lot easier! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Full Name Column to To Two Columns (2003)

    If the check box "Extend data range formats and formulas" in the Edit tab of Tools | Options is ticked, Excel automatically copies formatting and formulas to a new row.

  9. #9
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Full Name Column to To Two Columns (2003)

    Many thanks, Hans! Brilliant! Works great! There were a couple of names in my sample list that had quote marks in the column for full name (indicating a nick name, I guess), which caused an error when I ran the macro the first time, but once I cleaned up those quotes/nick names and ran the macro again, it worked great! Thanks for all your help. You're the best!...Mary

  10. #10
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Full Name Column to To Two Columns (2003)

    An alternative way, to use formula in extract the last name

    C2, copied down

    =IF(--FIND(".",A2&".")>=LEN(A2),SUBSTITUTE(A2,B2&" ",""),MID(A2,FIND(".",A2)+2,255))

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Full Name Column to To Two Columns (2003)

    Hans,

    Your VBA solution is quite amazing. What if an individual has a middle name or initial? How would the code be modified to reflect the middle name or middle initial in a third column.

    Thanks,
    John

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Full Name Column to To Two Columns (2003)

    If there can be middle names as well as middle initials, it becomes quite complicated, since it is difficult to determine whether someone has a middle name or a double last name. You might as well use Data | Text to Columns and process the exceptions manually.

Posting Permissions

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