removing special characters

[johnmerlino]

Hey all, right now I have this excel calculation:

[SIZE=2]=IF( ISERROR( SEARCH("&", A1) ), IF( ISERROR( SEARCH(" ", A1) ), A1, LEFT(A1,LOOKUP(2^15,FIND(" ",A1,ROW(INDIRECT("1:"&LEN(A1)))))-1)),SUBSTITUTE(A1,MID(A1,FIND(",",A1,1)+1,FIND("&",A1,1)-FIND(",",A1,1)),""))[/SIZE]

This effectively converts the below:
AVERNA,ROBERT C H/E
Corn,Marlin
Lizand,ROBERT H
CATANESE,SALVATORE J & OLGA M
JIMENEZ,ALEJANDRO & HILDA M

into this:

AVERNA,ROBERT C
Corn,Marlin
Lizand,ROBERT H
CATANESE,OLGA M
JIMENEZ,HILDA M

Hence, if there is a character after middle initial or middle name, it is stripped out, and if there is no middle initial, full nam is kept, and if there is an & sign the first name and middle initial to the right of & sign along with the last name to the left is brought into its own column.

I am having an issue in that there are 3 special strings (REV, TR, H/E) that must always be removed. So even if the middle name or initial is REV or TR or H/E, it doesn't matter. I want to remove them. In other words:

This:
Corn,Marlin REV TR H/E
White,Alexia & John M REV TR

needs to be converted to this:

Corn,Marlin
White,John M

And I would like to to be part of the existing query I have:

[SIZE=2]=IF( ISERROR( SEARCH("&", A1) ), IF( ISERROR( SEARCH(" ", A1) ), A1, LEFT(A1,LOOKUP(2^15,FIND(" ",A1,ROW(INDIRECT("1:"&LEN(A1)))))-1)),SUBSTITUTE(A1,MID(A1,FIND(",",A1,1)+1,FIND("&",A1,1)-FIND(",",A1,1)),""))[/SIZE]

Thanks for any response.

 

[poolhall]

If you use Find and Replace to replace " H/E", " TR", " REV" with a space, wouldn't it be good enough for you?

 

[johnmerlino]

Inside the calculation do a find and replace?

 

[johnmerlino]

Actually, that seems to have worked pretty well. Thanks