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.