Excel VBA: Split First Name and Last Name (3 Practical Examples)

This article illustrates several approaches that split a full name into its first name and last name components using VBA code in Excel. We’ll use Excel’s built-in Split, InStr, InStrRev Left, and Right functions to configure our code. Let’s dive into the examples to explore the techniques that can get your job done quickly and easily.

Excel VBA: Split First Name and Last Name: 3 Examples

Introduction to VBA Split, InStr, InStrRev, Left, Right Functions

VBA Split Function: The Split in Excel VBA 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]]])

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

delimiter– A string 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– It represents the number of substrings to return in the output. If omitted, the function will return all the substrings.

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

VBA InStr Function: We use the InStr function in Excel VBA to search a specific string within a given string from a predefined position. The syntax is-

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

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

VBA InStrRev Function: The 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])

Here,
StringCheck– The given string from which we want to find the position of the desired string.
StringMatch– The desired string.
[start]- Stating position. The default search is right to left if omitted.
|[compare]- The type of comparison. The default is Binary Comparison.

VBA Left Function: The 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: The VBA Right function uses a number as input to return the given number of characters from the right side of a string.

Write Code in Visual Basic Editor

To split the first name and last name, we need to open and write VBA code in the Visual Basic editor. Follow the steps to open the Visual Basic Editor and write some code there.

• Go to the Developer tab from the Ribbon.
• Click the Visual Basic option.

• In the Visual Basic For Applications window, click the Insert dropdown to select the New Module option.

Now put your code inside the visual code editor and press F5 to run it.

1. Use of VBA Split Function to Separate First Name and Last Name in Excel

Task: Split the names in range B5:B9 into first name and last name components.

Solution: Use the Split function without any delimiter as the first and last names are separated with space. In Addition, the Split function returns a zero-based (start from 0) one-dimensional array. Each element of the array is a substring separated by a predefined delimiter. So, to solve the above task, we need to output the 1st element of the array as the First Name and the 2nd element as the Last Name.

Code: Insert 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

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

Note: Similarly, we can separate first name, middle name, and last name easily using the Split function. The middle name will be just another element of the array in that case.

Read More: How to Split Names with Comma in Excel

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

Task: Split the names in range B5:B9 into first name and last name components.

Solution: Use the Left and Right functions respectively to get the first name and last name from the full name. The full name uses a space character to separate its components. That’s why we’ll search for the space character using the InStr and InStrRev functions in our VBA code.

Code: Insert 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

Output: The above code splits names in two column i.e. first name in cells C5:C9 and the last name in cells D5:D9.

Note: We could also split names into three columns if there is a middle name in the 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

Task: The full names in cells B5:B9 are in the following form-

[LastName SuffixName, FirstName MiddleName]

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

Code: Insert 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

Output: The output is in the following screenshot.

Things to Remember

• If the specified delimiter doesn’t exist in the source string, the Split function will return the string as it is.
• If the compare argument of the Split function is omitted, the default value is
• The InStrRev function returns 0 if the substring doesn’t exist in the given string.

Download Practice Workbook

Conclusion

Now, we know how to split the first name and the last name from a full name using VBA in Excel with the help of suitable examples. Hopefully, it will help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

Related Articles

• How to Split Names Using Formula in Excel
• Separate First and Last Name with Space Using Excel Formula