Basically, what I'm trying to do is I have this excel formula:
=IF(ISNUMBER(FIND(",",A1,FIND(",",A1,FIND("&",A1,FIND("&",A1))))+1),TRIM(MID(A1,FIND("&",A1,1)+1,FIND("&",A1,FIND("&",A1,1)+1)-FIND("&",A1,1)-1)),IF(ISNUMBER(FIND(",",A1,FIND(",",A1)+1)),TRIM(MID(A1,FIND("&",A1)+1,FIND("&",A1)-3)),IF(ISNUMBER(FIND("&",A1,FIND("&",A1)+1)),CONCATENATE(LEFT(A1,FIND(",",A1)-1),",",TRIM(MID(A1,FIND("&",A1,1)+1,FIND("&",A1,FIND("&",A1,1)+1)-FIND("&",A1,1)-1))),IF(ISNUMBER(FIND("&", A1)),SUBSTITUTE(A1,MID(A1,FIND(",", A1)+1,FIND(" &", A1)-FIND(",", A1)+2), ""),A1))))
if you notice, this part of it:
=IF(ISNUMBER(FIND(",",A1,FIND(",",A1,FIND("&",A1,FIND("&",A1))))+1)
I'm trying to say if the cell contains two commas and two ampersands, then do something. Otherwise, I specify it to do something else. Unfortunately, this condition doesn't behave as expected. Although I have cells with two commas and only a single ampersand:
CORNWALL,A ROBERT & CORNWALL,ANNA
it treats this as true, and applies the subsequent true statement to the above cell and therefore causes a VALUE error because the subsequent true statement doesn't account for the cell above. It only accounts for cells with two commas and two ampersands. I already have another ISNUMBER condition for the cell above, as you can see in my initial formula.
Is there a way to fix my ISNUMBER condition so in the first level of execution, it checks for both two commas and two ampersands in the cell?
Thanks for response.
I don't know if I can see what's wrong with your code there, but have you tried a different approach to counting the number of ampersands and commas?
=IF(AND(LEN(A1)-LEN(SUBSTITUTE(A1,"&",""))=2,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))=2),TRUECOND,FALSECOND)
This just seems a little simpler.