Parsing first from last name

[jmc1988]

I've found several similar posts but nothing that will do exactly what I need. I have a list of several names that are in the form of FirstLast or FirstMiddleLast (no spaces, beginning of each name capitalized). I also have a separate file with these same names separated and put into 2 separate columns. I need to separate the FirstLast names into a firstname and lastname string so that I can search for exact matches in the other file with the names already in separate columns which I can do once I get the names separated.

To complicate issues, some first names are initials like J.J., and some people do not have middle names, and some middle names are initials. So I would need to be able to come up with strings for each of the following examples:

JohnHarris
ScottLeeBrown
MikeP.Smith
J.J.Robinson
JamesS.P.Black

that return:

First Last
John Harris
Scott Lee Brown
Mike P. Smith
J.J. Robinson
James S.P. Black

The forum deleted the formatting, but the last name will always just be one name and the first will be everything else.

One way I was thinking that may be easiest (if possible) is to search for the last capital letter and turn the rest of the string following it into the lastname and then take the rest and put it into the first name, then separate the first from the middle name in that string. Thanks in advance for any help.

[StephenR]

I think this gets you at least part of the way there.

Sub x()

Dim oMatches As Object, n As Long, rng As Range

For Each rng In Range("A1", Range("A1").End(xlDown))
    With CreateObject("vbscript.regexp")
        .Global = True
        .Pattern = "[A-Z]"
        Set oMatches = .Execute(rng)
    End With
    n = oMatches(oMatches.Count - 1).firstindex
    rng.Offset(, 1) = Left(rng, n)
    rng.Offset(, 2) = Right(rng, Len(rng) - n)
Next rng

End Sub