johnmerlino
Board Regular
- Joined
- Sep 21, 2010
- Messages
- 94
Hey all,
I have a list that looks like this:
AVERNA,ROBERT C H/E
Corn,Marlin Calvin LIV
CATANESE,SALVATORE J & OLGA M
JIMENEZ,ALEJANDRO & HILDA M
Cortes,Alessandra M Marquez
CATANESE,SALVATORE J & OLGA M & Nunez, Jason P
The conditions:
* If comma delineater between last and first name and there is no ampersand (&) in field, then delete everything after the middle initial/name, if the contents after the middle initial/name is three characters or less.
* If comma delineater between last and first name and there is an ampersand (&) in field, concatenate the last name, which is always to the far left, to the first and middle initial (if one exists) located after the ampersand into the second column and remove everything after the ampersand, including the ampersand in the first column. In otherwords, we have two names in two different columns, both with same last name.
* If comma delineater between last and first name and there is a string more than 3 characters long after the middle initial, then move the entire column exactly as it is into the third column. This indicates that the person has two last names.
* If comma delineater between last and first name and there are two ampersands (&) in field, if the contents after one only has two strings where one to right is more than 3 characters, then move that two column 2 with the last name to far left and move the one after second ampersand to column 3 if same condiiton, but if that has a comma delineater, then don't concatenate it with name to far left, rather just keep it as is and move it to the third column. So this: CATANESE,SALVATORE J & OLGA M & Nunez, Jason P would look like this: CATANESE,SALVATORE J CATANESE, OLGA M Nunez, Jason P
Is it possible to place all these conditions in a single line so that I just apply to entire list?
I have a list that looks like this:
AVERNA,ROBERT C H/E
Corn,Marlin Calvin LIV
CATANESE,SALVATORE J & OLGA M
JIMENEZ,ALEJANDRO & HILDA M
Cortes,Alessandra M Marquez
CATANESE,SALVATORE J & OLGA M & Nunez, Jason P
The conditions:
* If comma delineater between last and first name and there is no ampersand (&) in field, then delete everything after the middle initial/name, if the contents after the middle initial/name is three characters or less.
* If comma delineater between last and first name and there is an ampersand (&) in field, concatenate the last name, which is always to the far left, to the first and middle initial (if one exists) located after the ampersand into the second column and remove everything after the ampersand, including the ampersand in the first column. In otherwords, we have two names in two different columns, both with same last name.
* If comma delineater between last and first name and there is a string more than 3 characters long after the middle initial, then move the entire column exactly as it is into the third column. This indicates that the person has two last names.
* If comma delineater between last and first name and there are two ampersands (&) in field, if the contents after one only has two strings where one to right is more than 3 characters, then move that two column 2 with the last name to far left and move the one after second ampersand to column 3 if same condiiton, but if that has a comma delineater, then don't concatenate it with name to far left, rather just keep it as is and move it to the third column. So this: CATANESE,SALVATORE J & OLGA M & Nunez, Jason P would look like this: CATANESE,SALVATORE J CATANESE, OLGA M Nunez, Jason P
Is it possible to place all these conditions in a single line so that I just apply to entire list?
