How to Split First Name and Last Name with Excel VBA

Introduction:

The VBA Split Function: is used to split a string into substrings. The function returns a zero-based one-dimensional array. Each element of the array is a substring separated by a predefined delimiter. The syntax of the VBA function is-

Split(expression, [delimiter, [limit, [compare]]])

expression– represents a text string that contains substrings and a delimiter. If the string is empty, the function will also return an empty array.

delimiter– A character that is used to split the string into substrings. If omitted the function will use a space character as the delimiter. And if it is an empty string, it’ll return the original string as the output.

limit– represents the number of substrings to return in the output. If omitted, the function will return all the substrings.

compare– has several values. You can use vbBinaryCompare for a case-sensitive delimiter and vbTextCompare for a case-insensitive delimiter in the Split function.

The VBA InStr Function: searches a specific string within a given string at a predefined position. The syntax is-

InStr([start], string 1, string 2, [compare])

[start]- The position from which it starts searching. The default is 1 if omitted.
string 1– The string in which the function searches for a specified string.
string 2- The specific string that the function searches.
[compare]- The type of comparison. The default is a Binary Comparison.

VBA InStrRev Function: searches the position of a substring within a given string, from the end of the string. The syntax is-

InStrRev(StringCheck, StringMatch, [start],[compare])

StringCheck– The given string in which you want to find the position of the specified string.
StringMatch– The specified string.
[start]- Stating position. The default search is right to left if omitted.
|[compare]- The type of comparison. The default is a Binary Comparison.

VBA Left Function: uses a number as input to return the given number of characters from the left side of a string.

VBA Right Function: uses a number as input to return the given number of characters from the right side of a string.

Entering a Code in the Visual Basic Editor

Go to the Developer tab.
Click Visual Basic option.

In the Visual Basic For Applications window, click Insert and select Module.

Enter the code inside the visual code editor and press F5 to run it.

Example 1 – Using the VBA Split Function to Separate First and Last Name in Excel

Split the names in B5:B9.

Use the Split function without delimiters (there’s a space between the first and last names).

The Split function returns a zero-based (starting from 0) one-dimensional array. Each element of the array is a substring separated by a predefined delimiter. Enter the 1st element of the array as the First Name and the 2nd element as the Last Name.

Use the following code in the Visual Basic editor and press F5 to run it.

Sub SplitFirstLastName()
For i = 5 To Range("B" & Rows.Count).End(xlUp).Row
Range("C" & i).Value = Split(Range("B" & i))(0)
Range("D" & i).Value = Split(Range("B" & i))(1)
Next
End Sub

The code splits the full name into the first name in C5:C9 and last name in D5:D9.

Note: You can separate first name, middle name, and last name using the Split function. The middle name will be another element of the array.

Method 2 – Split First Name and Last Name by Merging the VBA Left and VBA Right Functions

Split the names in B5:B9.

Use the Left and Right functions to get the first and last name. There is a space separating first and last name. Search for the space character using the InStr and InStrRev functions in the VBA code.

Use the following code in the Visual Basic editor and press F5 to run it.

Sub SplitFirstLastName()
Dim FN, LN As String
For i = 5 To Range("B" & Rows.Count).End(xlUp).Row
FN = Left(Range("B" & i), InStr(Range("B" & i), " ") - 1)
LN = Right(Range("B" & i), Len(Range("B" & i)) - InStrRev(Range("B" & i), " "))
Range("C" & i) = FN
Range("D" & i) = LN
Next
End Sub

The code splits names in two columns: first name in C5:C9 and last name in D5:D9.

Note: You can also split names into three columns if there is a middle name, using the following line of code after declaring a new variable: MD as a string.

MD = Mid(Range("B" & i), InStr(Range("B" & i), " ") + 1, InStrRev(Range("B" & i), " ") - InStr(Range("B" & i), " ") - 1)

3. Split and Extract First, Middle, Last & Suffix Names from Full Name

The full names in B5:B9 are in the following form:

[LastName SuffixName, FirstName MiddleName]

Split the first, middle, last name, and the suffix in a table.

Enter the following code in the visual basic editor and press F5 to run it.

Sub SplitFirstMiddleLastSuffixNames()
Dim FN, LN, MN, SufN As String
Dim StrLocation, StrLocation2, StrLocation3 As Integer
For i = 5 To Range("B" & Rows.Count).End(xlUp).Row
StrLocation = InStr(1, Range("B" & i), ",", vbTextCompare)
If StrLocation = 0 Then
    StrLocation = Len(Range("B" & i)) + 1
End If
LN = Trim(Left(Range("B" & i), StrLocation - 1))
StrLocation2 = InStr(1, LN, " ", vbTextCompare)
If StrLocation2 Then
    StrLocation3 = InStr(StrLocation2 + 1, LN, " ", vbTextCompare)
    If StrLocation3 Then
        SufN = Right(LN, Len(LN) - StrLocation3)
        LN = Left(LN, StrLocation3 - 1)
    Else
        SufN = Right(LN, Len(LN) - StrLocation2)
        LN = Left(LN, StrLocation2 - 1)
    End If
End If
StrLocation2 = InStr(StrLocation + 2, Range("B" & i), " ", vbTextCompare)
If StrLocation2 = 0 Then
    StrLocation2 = Len(Range("B" & i))
End If
If StrLocation2 > StrLocation Then
    FN = Mid(Range("B" & i), StrLocation + 1, StrLocation2 - StrLocation)
    MN = Right(Range("B" & i), Len(Range("B" & i)) - StrLocation2)
End If
StrLocation = InStr(1, LN, "-", vbTextCompare)
If StrLocation Then
    LN = Trim(StrConv(Left(LN, StrLocation), vbProperCase)) & _
    Trim(StrConv(Right(LN, Len(LN) - StrLocation), vbProperCase))
Else
    LN = Trim(StrConv(LN, vbProperCase))
End If
FN = Trim(StrConv(FN, vbProperCase))
MN = Trim(StrConv(MN, vbProperCase))
SufN = Trim(StrConv(SufN, vbProperCase))
Select Case UCase(SufN)
    Case "JR", "SR", "I", "II"
    Case Else
        If Not IsNumeric(Left(SufN, 1)) Then
            LN = LN & " " & SufN
            SufN = ""
        End If
End Select
Range("C" & i) = FN
Range("D" & i) = MN
Range("E" & i) = LN
Range("F" & i) = SufN
Next
End Sub

This is the output.