Extract String After Second Occurrence of Delimiter

Gurus,

I have a column of full names, and each entry in the column could look like one of these:

lastname, firstname middlename
lastname, firstname
lastname, firstname middleinitial
hyphenated-lastname, firstname
hyphenated-lastname, firstname, middleinitial
hyphenated-lastname, firstname, middle name
firstname lastname
firstname middleinitial lastname
firstname middlename lastname
firstname secondname middlename lastname
lastname suffix, firstname middlename
lastname suffix, firstname
lastname suffix, firstname middleinitial
hyphenated-lastname suffix, firstname
hyphenated-lastname suffix, firstname, middleinitial
hyphenated-lastname suffix, firstname, middle name
firstname lastname suffix
firstname middleinitial lastname suffix
firstname middlename lastname suffix
firstname secondname middlename lastname suffix

I know; my client has a problem standardizing their data...

So, what I need, at the end of the day, is firstname secondname in the original column, and lastname suffix in the next column. All of the middlenames or middleinitials can be stripped.

I envision selecting the name column cells (they vary in number) and running a VBA macro that I've added to the command ribbon.

Super appreciate any help!

If it is a comma, it is last, first. No comma, it is first last. There are rules, I can parse them every time - just was hoping to automate it.

Sorry, you missed the point. With a comma, the last name is always first (with or without suffix), followed by the first name and initial or another name. Without a comma, it is always first name first, middle or second name(s), and last name (with or without suffix). The examples I provided are all inclusive and define the rule sets.

You need to refine sfix or list all suffixes in range.

Sub test()
    Dim a, i As Long, sfix As String
    sfix = "sr.|jr.|phd|etc"
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        a = .Value
        With CreateObject("VBScript.RegExp")
            .Global = True: .IgnoreCase = True
            .Pattern = "([$^()\\{}\[\]+*?.-])"
            sfix = "(" & .Replace(sfix, "\$1") & ")"
            For i = 1 To UBound(a, 1)
                .Pattern = "^([^, -]+-)?([^, .-]+) " & sfix & ", (\S+).*"
                If .test(a(i, 1)) Then
                    a(i, 1) = .Replace(a(i, 1), "$4 $2")
                Else
                    .Pattern = "^(\S+).* (\S+) " & sfix & "$"
                    If .test(a(i, 1)) Then
                        a(i, 1) = .Replace(a(i, 1), "$1 $2")
                    Else
                        .Pattern = "^([^, -]+-)?([^, ]+), (\S+).*"
                        If .test(a(i, 1)) Then
                            a(i, 1) = .Replace(a(i, 1), "$3 $2")
                        Else
                            .Pattern = "^(\S+).* (\S+)$"
                            If .test(a(i, 1)) Then
                                a(i, 1) = .Replace(a(i, 1), "$1 $2")
                            End If
                        End If
                    End If
                End If
            Next
        End With
        .Columns("b") = a
    End With
End Sub

Let's refine; anything left of a comma is a last name, anything to the right of a comma (to the last space in the string) is the first name, anything after a second space can be truncated.

If there is no comma, everything to the left of the first space is the first name (unless it is MR, MRS, or MS - those can be truncated), anything to the right of the last space is the last name - unless it is one of the following: JR, SR, I, II, III - then it is part of the last name.

Post a workbook with possible different examples and the result that you want.

Yes, those are similar to the formulas I'm currently using, but I'm trying to build a VBA macro that I can put in the command ribbon so I can off-load this task to a clerk -- one step instead of many (with the ever-present problem of basic Excel-user tendencies to break formulas).