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
=======
