nested iserror gives error

[johnmerlino]

Hey all,

I have a query:

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

It converts this:

AVERNA,ROBERT C        
Corn,Marlin       
Lizand,ROBERT H        
CATANESE,SALVATORE J & OLGA M       
JIMENEZ,ALEJANDRO & HILDA M     
Martin,Harry & Marie Anne Hernara

into this:

AVERNA,ROBERT C       
Corn,Marlin       
Lizand,ROBERT H       
CATANESE, OLGA M       
JIMENEZ, HILDA M     
Martin, Marie Anne Hernara

Problem is with this:

Sarah Ann Little Junior

it converts it incorrectly into this:

Sarah Ann Little

when I'd want to keep it just as this:

Sarah Ann Little Junior

I tried adding a nested iserror to return original row if it doesn't find a comma:

=IF( ISERROR( SEARCH("&", A1) ),IF( ISERROR( SEARCH(",", A1)),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)),""))

But it gives me this:

#VALUE!

Thanks for response.

 

[AlphaFrog]

Try this...

=IF(ISNUMBER(FIND("&", J2)),
      SUBSTITUTE(J2,MID(J2,FIND(",", J2)+1,FIND(" &", J2)-FIND(",", J2)+2), ""),J2)