# Thread: Full Name Column to To Two Columns (2003)

1. ## 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. ## 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.

3. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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
•