MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Dropping the Middle Initial


Posted by Bill Morris on October 03, 2001 11:09 AM

I have a column of names in this format: Public, John Q

Some of the names don't have a middle initial though.

How do I drop the middle initial from the ones that
have one?

Thanks.


Posted by Barrie Davidson on October 03, 2001 11:15 AM

Assuming you the middle initial will consist of one character only (no periods, etc.), this formula should work for you.

=IF(ISERROR(FIND(" ",RIGHT(A1,LEN(A1)-FIND(",",A1)-1))),A1,LEFT(A1,LEN(A1)-2))

Barrie
Barrie Davidson

Posted by Aladin Akyurek on October 03, 2001 11:18 AM

If the format of the full names follows the pattern of the example, you can use:

=SUBSTITUTE(A1," "&RIGHT(A1),"")

Aladin

Posted by Aladin Akyurek on October 03, 2001 11:27 AM

To finish what I've started...

I didn't take into account the case of "no middle initials", so the formula must be changed to:

=IF(RIGHT(RIGHT(A1))=" ",SUBSTITUTE(A1," "&RIGHT(A1),""),A1)

This can handle:

Public, John Q
Akyurek, Aladin
Akyurek, A.

but not:

Public, J. Q.
Public, J.Q.

Aladin

=======