How to create custom Excel functions by using VBA to automatically split full names into first, middle, and last name components. Improve your data analysis and reporting by efficiently parsing names with this helpful tutorial.
VBA function that you can use to extract the first name from a full name:
Function ExtractFirstName(fullName As String) As String
Dim firstSpace As Integer
Dim firstName As String
firstSpace = InStr(fullName, " ")
If firstSpace = 0 Then
ExtractFirstName = "Error: Invalid name format"
Exit Function
End If
firstName = Left(fullName, firstSpace - 1)
ExtractFirstName = firstName
End Function
This function takes a full name as input and returns the first name as output. If the input string does not contain a space, the function will return an error message.
To use this function, you can follow these steps:
- Open a new or existing workbook in Microsoft Excel.
- Press Alt + F11 to open the Visual Basic Editor.
- In the Visual Basic Editor, click on “Insert” from the menu bar, then select “Module” to create a new module.
- In the new module, paste the above code.
- Save the module with a meaningful name, such as “FirstNameExtractor.”
- Now you can use the function in any worksheet in your workbook. To extract the first name from a full name, simply enter the following formula into any cell:
=ExtractFirstName("full name")
Replace “full name” with the cell reference or text string that contains the full name you want to extract the first name from. The function will return the first name as output.
Note that this function assumes that the full name contains a first name and a last name separated by a space. If the full name contains a middle name or more than one space, the function will not work correctly.
Refer below example

ExtractFirstName
VBA code for a function that extracts the last name from a full name:
Function ExtractLastName(fullName As String) As String
Dim lastSpace As Integer
Dim lastName As String
lastSpace = InStrRev(fullName, " ")
If lastSpace = 0 Then
ExtractLastName = "Error: Invalid name format"
Exit Function
End If
lastName = Right(fullName, Len(fullName) - lastSpace)
ExtractLastName = lastName
End Function
This function takes a full name as input and returns the last name as output. If the input string does not contain a space, the function will return an error message.
To use this function, you can follow the same steps as for the previous function:
- Open a new or existing workbook in Microsoft Excel.
- Press Alt + F11 to open the Visual Basic Editor.
- In the Visual Basic Editor, click on “Insert” from the menu bar, then select “Module” to create a new module.
- In the new module, paste the above code.
- Save the module with a meaningful name, such as “LastNameExtractor.”
- Now you can use the function in any worksheet in your workbook. To extract the last name from a full name, simply enter the following formula into any cell:
=ExtractLastName("full name")
Replace “full name” with the cell reference or text string that contains the full name you want to extract the last name from. The function will return the last name as output.
Note that this function assumes that the full name contains
Refer below example

=ExtractLastNam
VBA code for a function that extracts the middle name from a full name:
Function ExtractMiddleName(fullName As String) As String
Dim firstSpace As Integer
Dim lastSpace As Integer
Dim middleName As String
firstSpace = InStr(fullName, " ")
lastSpace = InStrRev(fullName, " ")
If firstSpace = 0 Or lastSpace = 0 Or firstSpace = lastSpace Then
ExtractMiddleName = ""
Exit Function
End If
middleName = Mid(fullName, firstSpace + 1, lastSpace - firstSpace - 1)
ExtractMiddleName = middleName
End Function
This function takes a full name as input and returns the middle name as output. If the input string does not contain a middle name (i.e., there is only one name or no spaces in the name), the function will return an empty string.
To use this function, you can follow the same steps as for the previous functions:
- Open a new or existing workbook in Microsoft Excel.
- Press Alt + F11 to open the Visual Basic Editor.
- In the Visual Basic Editor, click on “Insert” from the menu bar, then select “Module” to create a new module.
- In the new module, paste the above code.
- Save the module with a meaningful name, such as “MiddleNameExtractor.”
- Now you can use the function in any worksheet in your workbook. To extract the middle name from a full name, simply enter the following formula into any cell:
=ExtractMiddleName("full name")
Replace “full name” with the cell reference or text string that contains the full name you want to extract the middle name from. The function will return the middle name as output, or an empty string if there is no middle name.
Note that this function assumes that the full name contains a first name, a middle name (if present), and a last name, separated by spaces. If the full name does not follow this format, the function may not work correctly.
Refer below example

ExtractMiddleNam
VBA Code snap
