How to Create Custom Excel Functions to Extract First, Middle, and Last Names.

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:

1. Open a new or existing workbook in Microsoft Excel.
2. Press Alt + F11 to open the Visual Basic Editor.
3. In the Visual Basic Editor, click on “Insert” from the menu bar, then select “Module” to create a new module.
4. In the new module, paste the above code.
5. Save the module with a meaningful name, such as “FirstNameExtractor.”
6. 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

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:

1. Open a new or existing workbook in Microsoft Excel.
2. Press Alt + F11 to open the Visual Basic Editor.
3. In the Visual Basic Editor, click on “Insert” from the menu bar, then select “Module” to create a new module.
4. In the new module, paste the above code.
5. Save the module with a meaningful name, such as “LastNameExtractor.”
6. 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

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:

1. Open a new or existing workbook in Microsoft Excel.
2. Press Alt + F11 to open the Visual Basic Editor.
3. In the Visual Basic Editor, click on “Insert” from the menu bar, then select “Module” to create a new module.
4. In the new module, paste the above code.
5. Save the module with a meaningful name, such as “MiddleNameExtractor.”
6. 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

VBA Code snap

You May Also Like