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!

 

[István Hirsch]

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.

The OP did not ask handling „Rowling J. K.” type structures. But if he did I would suggest:

=IFERROR(REPLACE(A1,SEARCH(" ?#",SUBSTITUTE(A1,".","")&"#"),3,""),””&A1)

I think it is not highly unusual to let the formula display #VALUE error if it bumps into an empty cell (for example, almost all formulae in this thread give „#VALUE” for empty cells). However, it can be overcome by inserting the red part.

 

[Rick Rothstein]

The OP did not ask handling „Rowling J. K.” type structures.

True, but I find it quite common that OPs fail to tell us everything we need to know about their data. I would also note that I started my comment with "If"... I was not criticizing your formula per se, just noting a possible problem in case it meant something to the OP.

I think it is not highly unusual to let the formula display #VALUE error if it bumps into an empty cell (for example, almost all formulae in this thread give „#VALUE” for empty cells). However, it can be overcome by inserting the red part.

I am quite embarrassed by my comment on this one. My first posted formula does not display the #VALUE! error for missing data, then I forgot to check for it in the formulas I posted afterwards. I usually like to hid the error if done easily, so with that in mind, here is my last posted formula modified to hide the error, again, in case that might matter to the OP...

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