Removing Middle Initial or Middle Name

[Mschweider]

I have a document where the name format is Last, First Middle Name/Initial. (Sometimes there is a period at the end of the middle initial, sometimes there is not, see below).

I was wondering if it was possible to remove the Middle name/initial using macros. I have found some help but there always seems to be something wrong in at least one case. The list of names is 1533 so I would like it to work without any input from me.

Some example name formats:
Smith, John
Smith, John L
Smith, John L.
Smith Dean, John (Smith Dean is two last names)
Smith Dean, John L
Smith Dean, John L.

I would like it to work with all of those difference since that is how the file comes to me, if possible.

This is the current formula I have found but it doesn't work with two last names:

=IF(LEN(A3)-LEN(SUBSTITUTE(A3," ",""))=1,A3,SUBSTITUTE(LEFT(A3,FIND("^^",SUBSTITUTE(A3," ","^^",2))-1),",","",2))

Thanks for any help!

 

[Jonmo1]

Try

=LEFT(A1,FIND(" ",A1&" ",FIND(",",A1)+2)-1)

 

[Mschweider]

Thanks! Works perfectly for what I needed. I found 1 Jr. in the list though. Is there anyone to modify it so it removes that?

 

[Jonmo1]

You're welcome.

Can you post a sample of the one with Jr, and what result you'd expect from it?

 

[Mschweider]

The format for the Jr. changes slightly from above, which is strange. If its not possible, it's only one case.

Example:
Smith, Jr, John L

 

[Jonmo1]

And what is your desired result for that example?

 

[Mschweider]

Sorry about that. I would like it to be the same format as above: Smith, John

 

[Jonmo1]

Try

=LEFT(SUBSTITUTE(A1,"Jr, ",""),FIND(" ",SUBSTITUTE(A1,"Jr, ","")&" ",FIND(",",SUBSTITUTE(A1,"Jr, ",""))+2)-1)

 

[Mschweider]

Works great! Thanks a lot!

 

[Rick Rothstein]

Here is a slightly shorter formula that also seems to work...

=TRIM(SUBSTITUTE(LEFT(A1,LEN(A1)-2*ISNUMBER(FIND(" ",RIGHT(A1,3)))),"Jr, ",""))