Hey all,

Basically I have one cell that can look like this:

IN RIGHT OF AKA PAUL A HERMAN,

And I want to have it look like this:

HERMAN PAUL A

So:

1) extract content to right of AKA
2) If comma exists at end, remove it
3) Rearrange the name so it's last, first, middle rather than first, middle, last

I have been working on this and this is what I have:

Sub Inspect()
    Dim old_str As String
    Dim new_str As String
    Dim first_name As String
    Dim last_name As String
    Dim middle_name As String
    Dim regex As Object
    Dim Rng As Range
    
    pos = InStr(Range("A1"), "AKA")
    
    If pos <> 0 Then
           old_str = Right(Range("A1"), Len(Range("A1")) - pos - 2)
    End If
    
    new_str = Left(old_str, Len(old_str) - 1)
    
    Range("B1").Value = Trim(new_str)
    
    Set Rng = Range("B1")

    Set regex = CreateObject("VBScript.RegExp")

    regex.Global = True
    regex.IgnoreCase = True
    regex.Pattern = "(^\w+\s\w+\s\w+)"

    If regex.Test(Range("B1").Value) Then
    
        first_name = Left(B1, Find(" ", B1) - 1)
        
        middle_name = Mid(B4, Find(" ", B4) + 1, Find(" ", B4, 1 + Find(" ", B4)) - Find(" ", B4) - 1)
    
        last_name = Right(A1, Len(A1) - Find("*", Substitute(A1, " ", "*", Len(A1) - Len(Substitute(A1, " ", "")))))
    End If
    
    Range("C1").Value = last_name & " " & first_name & " " & middle_name
End Sub
I get compiler error and it highlights the FIND function. It appears I cannot use excel formulas in a VBA script. Anyone have any solution how I can extract the first, last, and middle so I can rearrange them in my VBA function?

Thanks for response