johnmerlino
Board Regular
- Joined
- Sep 21, 2010
- Messages
- 94
Hey all, right now I have this excel calculation:
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:
Thanks for any response.
Code:
[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]
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:
Code:
[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]