How to find the last position of a specific character in a string with Excel VBA

   So I've been working on a parsing job that has a string that has several names in it. So far I've found no function that will return the last position of a specific character in a string so I present you with this:


Dim MyCell As Range
Dim i, j As Integer

Sub FindLastName()
Set MyCell = ActiveCell
For i = 1 To Len(MyCell.Text)
If InStr(i, MyCell.Text, " ") <> 0 Then
j = InStr(i, MyCell.Text, " ")
End If
Next i
MsgBox "Last Name: " & _
Right(MyCell.Text, Len(Replace(MyCell.Text, " ", "", j))), vbInformation, "Last Name"
End Sub

Basically what I did was find the length of the string and then use the Instr() function starting at every position in the string. What I accomplished is now I can take a string like "Andres Charles Simon Bob Smith" and pull out the guy's last name by finding the position of the last space " " in the string. This particular tidbit will look like this: