Excel 2007 All-In-One Desk Reference For Dummies
Book image
Explore Book Buy On Amazon

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.

About This Article

This article can be found in the category: