InStrRev not extracting data to right of last space in string

Hey all, I created this macro in VBA:

Sub MergeExplorer()

Dim main_name As String
Dim secondary_name As String
Dim secondary_first_name As String
Dim secondary_last_name As String
Dim first_and_last_together As Object
Dim lngLastRow As Long
Dim i

lngLastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Set first_and_last_together = CreateObject("VBScript.RegExp")

    With first_and_last_together
        .MultiLine = False
        .Global = True
        .IgnoreCase = True
        .Pattern = "^(\w{1,}\s)+&W(\s\w{1,})+$"
    End With
    
For i = 1 To lngLastRow


If first_and_last_together.test(Range("A" & i).Value) Then

pos = InStr(Range("A" & i), " &W ")

main_name = Mid(Range("A" & i).Value, 1, pos)

MsgBox main_name

secondary_first_name = Right(Range("A" & i).Value, Len(Range("A" & i)) - pos - 2)

secondary_last_name = Left(main_name, InStrRev(main_name, " ") + 1)

MsgBox secondary_first_name & " " & secondary_last_name

End If

Next i
 

End Sub

This line:

secondary_last_name = Left(main_name, InStrRev(main_name, " ") + 1)

is supposed to extract:

DIAZ

in:

ROGELIO P DIAZ

Instead, it doesn't extract anything.


For example in this:

ROGELIO P DIAZ &W CLUADIA L

This line:

MsgBox secondary_first_name & " " & secondary_last_name

is supposed to return this:

CLUADIA L DIAZ

rather it returns this:

CLUADIA L ROGELIO P DIAZ

Not sure why the instrrev function is not returning the data to the right of the last space in the string main_name *(e.g. ROGELIO P DIAZ) in order to extract DIAZ.

Thanks for response.