UPDATE ISNUMBER and nested FINDs not working as expected

**johnmerlino** · 11-07-2010, 08:00 PM

Hey all,

My initial post was too confusing. I will try to simplify.

I have this record:

HernaraMartin,Harry & Marie Anne & Ann Sally

Notice after the second ampersand, there's a name without the comma.

Now look at this record:

HernaraMartin,Harry & Smith,Marie Anne & Jenners,Ann Sally

Notice after the second ampersand, there's a name with a comma delimited last from first and middle initial.

I have this formula:

=IF(ISNUMBER(FIND("&",A16,FIND("&",A16)+1)),RIGHT(A16,LEN(A16)-FIND("*",SUBSTITUTE(A16,"&","*",LEN(A16)-LEN(SUBSTITUTE(A16,"&",""))))),"")

This successfully addresses the situation where there's a comma and outputs this:

 
Jenners,Ann Sally

However, in instance where there's no comma delimiting last and first name after the second ampersand, it outputs:

Ann Sally

This is wrong. It's supposed to output:

HernaraMartin,Ann Sally

In other words, if there's a comma, then we take the last name after the second ampersand, otherwise we pull the last name from the very beginning of cell.

Thanks for response.Attachment 89414

**zbor** · 11-08-2010, 03:02 AM

And what is input text?

Best would be to upload example excel workbook so you can get answer ASAP and we don't need to spend time retyping your data.
Thx.

**johnmerlino** · 11-08-2010, 10:07 PM

What I'm basically trying to figure out is how to determine if a comma exists after that second ampersand.

If it exists, then run this:

TRIM(RIGHT(A32,LEN(A32)-FIND("*",SUBSTITUTE(A32,"&","*",LEN(A32)-LEN(SUBSTITUTE(A32,"&",""))))))

If it doesn't, then run this:

=CONCATENATE(LEFT(A32,FIND(",",A32)-1),",",TRIM(LEFT(A32,FIND("&",A32,FIND("&",A32))+1)))

I know there's a handy formula like this:
=IF(AND(LEN(A1)-LEN(SUBSTITUTE(A1,"&",""))
But in this specific situation I don't know best way to determine whether a comma exists after second ampersand.

**johnmerlino** · 11-08-2010, 11:08 PM

I figured it out:

=IF(ISNUMBER(FIND(",",MID(A30,FIND("&",A30,FIND("&",A30)+1)+1,LEN(A30)))),TRIM(RIGHT(A30,LEN(A30)-FIND("*",SUBSTITUTE(A30,"&","*",LEN(A30)-LEN(SUBSTITUTE(A30,"&","")))))),CONCATENATE(LEFT(A30,FIND(",",A30)-1),",",TRIM(MID(A30,FIND("&",A30,FIND("&",A30)+1)+1,LEN(A30)))))

**squiggler47** · 11-10-2010, 02:13 AM

Ok given the data so far :-

KNUD J & MARIA L HOSTRUP
HOSTRUP, KNUD J & MARIA L
Sarah Ann Little Junior
Jenners,Ann Sally
HernaraMartin,Harry & Marie Anne & Ann Sally

Would need 3 formulas to split them up :-

=CHOOSE((LEN(B2)-LEN(SUBSTITUTE(B2,"&","")))*3+LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1,B2,B2,"C",RIGHT(B2,LEN(B2)-SEARCH("@",SUBSTITUTE(B2," ","@",LEN(B2)-LEN(SUBSTITUTE(B2," ","")))))&", "&LEFT(B2,FIND("&",B2&"&")-1),LEFT(B2,FIND(",",B2&",")-1)&", "&TRIM(SUBSTITUTE(SUBSTITUTE(B2,LEFT(B2,FIND(",",B2&",")),""),RIGHT(B2,LEN(B2)-FIND("&",B2&"&")+1),"")),"F","G",LEFT(B2,FIND(",",B2&",")-1)&", "&TRIM(SUBSTITUTE(SUBSTITUTE(B2,LEFT(B2,FIND(",",B2&",")),""),RIGHT(B2,LEN(B2)-FIND("&",B2&"&")+1),"")),"I")

=CHOOSE((LEN(B2)-LEN(SUBSTITUTE(B2,"&","")))*3+LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1,"","","C",RIGHT(B2,LEN(B2)-SEARCH("@",SUBSTITUTE(B2," ","@",LEN(B2)-LEN(SUBSTITUTE(B2," ","")))))&", "&SUBSTITUTE(RIGHT(B2,LEN(B2)-FIND("&",B2&"&")-1),RIGHT(B2,LEN(B2)-SEARCH("@",SUBSTITUTE(B2," ","@",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))))),""),LEFT(B2,FIND(",",B2&",")-1)&","&RIGHT(B2,LEN(B2)-FIND("&",B2&"&")),"F","G",LEFT(B2,FIND(",",B2&",")-1)&","&LEFT(SUBSTITUTE(B2,LEFT(B2,FIND("&",B2)),""),FIND("&",SUBSTITUTE(B2,LEFT(B2,FIND("&",B2)),""))-1),"I")

=CHOOSE((LEN(B2)-LEN(SUBSTITUTE(B2,"&","")))*3+LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1,"","","C","","","F","G",LEFT(B2,FIND(",",B2&",")-1)&","&RIGHT(B2,LEN(B2)-FIND("&",SUBSTITUTE(B2,"&"," ",1))),"I")

Basically what we have is several cases where "," or "&" are present or not, combining these :-

=CHOOSE((LEN(B2)-LEN(SUBSTITUTE(B2,"&","")))*3+LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1

gives us choices based on the number which is unique for each of the above cases this then selects the correct formula for each case in each column.

you will notice that some choices in the formula have letters "C","F","G","I" these are so far unknown cases if you get the letter showing up just create a formula to get the correct result and paste it in the formula over the letter in quotes!

Please find attached the file I used to create the formulas, the part formulas are highlighted in yellow, along with the final formula!