Thread: find the third space after an ampersand in cell

=IF(ISERR(FIND("&",A1,1)),"",IF(AND(ISERROR(FIND(",",A1)),IF(ISNUMBER(FIND("&",A1)))),ERROR OCCURS HERE,LEFT(A1,FIND("&",A1,1)-1)))



Where I say error occurs here, this is what's there:

LEFT(B3,FIND(",",B3&",")-1)&","&RIGHT(B3,LEN(B3)-FIND("&",B3&"&")),RIGHT(B3,LEN(B3)-SEARCH("@",SUBSTITUTE(B3," ","@",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))))&", "&SUBSTITUTE(RIGHT(B3,LEN(B3)-FIND("&",B3&"&")-1),RIGHT(B3,LEN(B3)-SEARCH("@",SUBSTITUTE(B3," ","@",LEN(B3)-LEN(SUBSTITUTE(B3," ",""))))),""))

which turns this:
KNUD J & MARIA L HOSTRUP

into this:
HOSTRUP,MARIA L

All it's supposed to do is if no ampersand, return empty cell, if no comma but ampersand exists, then the code which is containing errors right now and excel isn't telling me what problem is, and if there is no ampersand but there is a comma so we just return LEFT(A1,FIND("&",A1,1)-1).