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!

 

[Tacos_Tacos_Tacos]

The above solutions work quite nicely in the given context. However, I wonder if anyone has a solution that would work if some of the entries contain multiple first names (for example, "Smith, Ane Margot"). Unfortunately, the solutions above don't distinguish between the single letter of a middle initial and a longer string.

 

[Rick Rothstein]

The above solutions work quite nicely in the given context. However, I wonder if anyone has a solution that would work if some of the entries contain multiple first names (for example, "Smith, Ane Margot"). Unfortunately, the solutions above don't distinguish between the single letter of a middle initial and a longer string.

Can you show us a few representative example names (and the rearrangements you want for them) that none of the above solutions work for?

 

[Tacos_Tacos_Tacos]

Sure. Here is an example list that roughly shows the variety of entries I'm dealing with:

McGee, Bill
Van Dusen, Carl M
Jankowski, Phillip R
Corbett, Karen Ann
Frary, Mary Jane
Manire, Charles L
Hyun, Soo Kyu Wan Lee

My goal is to trim the middle (trailing) initial, if it exists. I've been searching for solutions for a while, but just can't seem to find anything that selectively eliminates single initials while sparing second, third, and fourth first-names. Ultimately, the names will be divided into two columns (last name, first name), but it seems to me that the first step is removing that middle initial.

Thank you for your help.

PS. In the end, the list should look like this:

McGee, Bill
Van Dusen, Carl
Jankowski, Phillip
Corbett, Karen Ann
Frary, Mary Jane
Manire, Charles
Hyun, Soo Kyu Wan Lee

 

[Rick Rothstein]

Give this formula a try...

=IF(MID(A1,LEN(A1)-1,1)=" ",LEFT(A1,LEN(A1)-2),A1)

 

[Tacos_Tacos_Tacos]

Tried, and functional. This is great. Thank you very much.

 

[István Hirsch]

Does it (formula #14) work for "Manire, Charles L." ?

 

[Tacos_Tacos_Tacos]

It worked for all cases.

 

[Rick Rothstein]

Does it (formula #14) work for "Manire, Charles L." ?

It worked for all cases.

Actually, István has a good point. I missed that some of your middle initials have periods after them... the formula I posted earlier will not work correctly for them (I am not sure why you are saying it does); however, this formula will...

=IF(MID(A1,LEN(A1)-1-(RIGHT(A1)="."),1)=" ",TRIM(LEFT(A1,LEN(A1)-2)),A1)

 

[István Hirsch]

Another version:

=IFERROR(REPLACE(A1,SEARCH(" ?.",A1&"."),4,""),A1)

 

[Rick Rothstein]

Another version:

=IFERROR(REPLACE(A1,SEARCH(" ?.",A1&"."),4,""),A1)

If the OP's list had this author's name, or names similarly constructed like it, then your formula would return an incorrect result...

Rowling, J. K.

Also, as written, your formula cannot be copied down past the end of existing data in anticipation for the future addition of more names as it returns a #VALUE! error if the cell is empty.