Parsing Names the VBA Way
Have you ever been given an Excel worksheet with names, in which the full names are all in one cell? Your task is to break the names into first name, last name — a process known as parsing. You can try using a formula and/or function to help, or you can use Visual Basic for Applications (VBA) code.
A common approach is to find the space between the first name and last name, and voila! – you know where the first name ends and the last name starts.
Unless you have names that are more complex in structure, such as Dr. William Healer or Zak H. Sneezer III.
Here is a typical VBA routine that looks through each name, character by character, until it finds a space. Then it places whatever is to the left of the space in one column and whatever is to the right of the space in the column after.
Do Until ActiveCell = " thename = ActiveCell.Value For looking = 1 To Len(thename) If Mid(thename, looking, 1) = " " Then ActiveCell.Offset(0, 1) = Left(thename, looking - 1) ActiveCell.Offset(0, 2) = Mid(thename, looking + 1) Exit For End If Next ActiveCell.Offset(1, 0).Activate Loop
This routine is helpful in parsing names with just one space, such as Harvey Wallbanger or Tom Collins. The following table shows the result of running the code on names with more than one space:
|Dr. William Healer||Dr.||William Healer|
|Zak H. Sneezer III||Zak||H. Sneezer III|
This is not a good parsing attempt. It worked out fine for parsing Harvey Wallbanger and Tom Collins but not so well for the other names.
Here is a more developed technique that returns better results. Bear in mind that parsing names is not perfect, but going about it this way does a better job.
The technique is to count how many spaces are in the full name and then break apart the name on one of the spaces. In particular, if there are three or more spaces, use the space two in from the right as the breaking space; otherwise, use the rightmost space as the breaking space.
This process is run in a VBA subroutine that calls a function while looping through the names. The number of spaces is determined early in the main loop, and the function returns the position where the breaking space is.
Sub parse_names() Dim thename As String Dim spaces As Integer Do Until ActiveCell = " thename = ActiveCell.Value spaces = 0 For test = 1 To Len(thename) If Mid(thename, test, 1) = " " Then spaces = spaces + 1 End If Next If spaces >= 3 Then break_space_position = space_position(" ", thename, spaces - 1) Else break_space_position = space_position(" ", thename, spaces) End If If spaces > 0 Then ActiveCell.Offset(0, 1) = Left(thename, break_space_position - 1) ActiveCell.Offset(0, 2) = Mid(thename, break_space_position + 1) Else ' this is for when the full name is just a single name with no spaces ActiveCell.Offset(0, 1) = thename End If ActiveCell.Offset(1, 0).Activate Loop End Sub Function space_position(what_to_look_for As String, what_to_look_in As String, space_count As Integer) As Integer Dim loop_counter As Integer space_position = 0 For loop_counter = 1 To space_count space_position = InStr(loop_counter + space_position, what_to_look_in, what_to_look_for) If space_position = 0 Then Exit For Next End Function
The following table shows what the result looks like now:
|Dr. William Healer||Dr. William||Healer|
|Zak H. Sneezer III||Zak H.||Sneezer III|
Name parsing is as much art as technical process. In this second example, all the first and last names are in the correct columns. If a new long name with five or more spaces were introduced, the routine might get the parsing wrong, and the VBA code would need more conditional testing put into it. But this example is now on the right track as an industrial-strength name-parsing routine.