Excel With VBA

How To Have VBA Parse A Name String

This post was actually an answer to someone’s question on one of my videos from a few years back.

https://www.youtube.com/watch?v=UspqR0g4XyU

The comment was:

How would you do this for the last name? Or second last name? How to find the space after the first space, and the space after the second space? And then, if there is no first space or there is a first space but no second space, then skip the function instead of error?

So the video was put out a few years back, and so I’m going to show you a newer way of handling this.

Here is the worksheet and a little explanation:

Basically, this is going to be one function, and one of my arguments is going to decide whether we need to parse the first name, last name, or middle name.

…And here are the answers to her questions:

How would you do this for the last name? 

Function ParseName(ParseText As String, NamePart As String)
    Dim strName As String
        
    Select Case NamePart
          
        Case "Last"
            'gets the name after the last space
            strName = Right(ParseText, Len(ParseText) - InStrRev(ParseText, " "))
        
    End Select
    
    ParseName = strName
    
End Function

Or second last name?

Since that may mean “middle” name, I’m going to show that.

The following will answer these 2 questions as well:

How to find the space after the first space, and the space after the second space?

And then, if there is no first space or there is a first space but no second space, then skip the function instead of error?

Function ParseName(ParseText As String, NamePart As String)
    Dim strName As String
    
    Dim varName As Variant
    Dim intSpaceCount As Integer
    
    Dim intFirstSpace As Integer
    Dim intLastSpace As Integer
    
    Select Case NamePart

            
        Case "Middle"
            'parse the entire string by all the spaces in the string using the split function.
            'then we will parse the check if there is a middle name based on the space count.

            varName = Split(ParseText, " ")
            
            For intSpaceCount = 0 To UBound(varName)
                If intSpaceCount = 1 Then
                    'no middle name
                End If
                If intSpaceCount = 2 Then
                    'get middle name
                    intFirstSpace = InStr(ParseText, " ")
                    intLastSpace = InStrRev(ParseText, " ")
                    
                    strName = Mid(ParseText, intFirstSpace + 1, intLastSpace - intFirstSpace - 1)
                End If
            Next
    End Select
    
    ParseName = strName
    
End Function

Here’s the code all together:

Function ParseName(ParseText As String, NamePart As String)
    Dim strName As String
    
    Dim varName As Variant
    Dim intSpaceCount As Integer
    
    Dim intFirstSpace As Integer
    Dim intLastSpace As Integer
    
    Select Case NamePart
        
        Case "First"
            'gets the name before the first space
            strName = Left(ParseText, InStr(ParseText, " "))
            
        Case "Middle"
            varName = Split(ParseText, " ")
            
            For intSpaceCount = 0 To UBound(varName)
                If intSpaceCount = 1 Then
                    'no middle name
                End If
                If intSpaceCount = 2 Then
                    'get middle name
                    intFirstSpace = InStr(ParseText, " ")
                    intLastSpace = InStrRev(ParseText, " ")
                    
                    strName = Mid(ParseText, intFirstSpace + 1, intLastSpace - intFirstSpace - 1)
                End If
            Next
    
        Case "Last"
            'gets the name after the last space
            strName = Right(ParseText, Len(ParseText) - InStrRev(ParseText, " "))
        
    End Select
    
    ParseName = strName
    
End Function

Let me know if you have any questions, and make sure you share this with someone else.

admin