Formula to split names, some with some without middle initial

[sitewolf]

I need to compare lists of names that come in from 2 sources, neither of which I have control of and I need to make sure the names on the 2nd source are also on the first. The problem is, the control source sometimes includes middle initials while the other does not. Fortunately the format is the same otherwise. So my thinking is, if I can extract the middle initial from the control source, I'm golden.

Format for both starts with firstname, lastname
....but the control source sometimes has firstname, lastname middleinitial

I can use a combo of left and mid formulas to do it if there IS a middle initial, but I'm not finding a method to do it ONLY if there's a middle initial.

In other words I might have:
Smith, John A
Jones, Mark
Anderson, Sam B
Davis, Al

Extra being lastname may contain a space, theoretically I guess firstname could, too....i.e. Miller Jr, Brad A

What formula would extract lastname, firstname whether or not there's a middle initial?
Or what formula would extract the last 2 characters only if the 2nd to the last is a space?

 

[Gerald Higgins]

This might help you along . . .

=if(left(right(a1,2),1)=" ",.....

Basically, it says look at the rightmost 2 characters, then of those 2 characters, IF the first one is a space character . . . do something, if not, do something else.

You could incorporate something like this into your existing formulas.