Aqui está a minha solução. Que usa em parte este .
=LEFT(A6,FIND(" ",A6,FIND(" ",A6,FIND(" ",A6)+1)+1))&RIGHT(A6,LEN(A6)-FIND("@",SUBSTITUTE(A6," ","@",LEN(A6)-LEN(SUBSTITUTE(A6," ","")))))
Isso mantém as primeiras 3 palavras e a última palavra e não precisa presumir que as primeiras três palavras são "Mr. and Mrs.". Se você supuser que pode usar a versão mais curta:
="Mr. and Mrs. "&RIGHT(A6,LEN(A6)-FIND("@",SUBSTITUTE(A6," ","@",LEN(A6)-LEN(SUBSTITUTE(A6," ","")))))
Editar : usando minha fórmula principal e esta (que apenas fornece a primeira e a última palavra):
=LEFT(A6,FIND(" ",A6))&RIGHT(A6,LEN(A6)-FIND("@",SUBSTITUTE(A6," ","@",LEN(A6)-LEN(SUBSTITUTE(A6," ","")))))
Podemos combiná-los com uma declaração if ... Não sei qual declaração seria a melhor, mas deixe-me mostrar a você como seria a contagem de "." na declaração. Se for 2 ou maior, use as 3 primeiras palavras e a última, senão a primeira e a última palavra.
IF(LEN(A6)-LEN(SUBSTITUTE(A6,".",""))>=2,LEFT(A6,FIND(" ",A6,FIND(" ",A6,FIND(" ",A6)+1)+1))&RIGHT(A6,LEN(A6)-FIND("@",SUBSTITUTE(A6," ","@",LEN(A6)-LEN(SUBSTITUTE(A6," ",""))))),LEFT(A6,FIND(" ",A6))&RIGHT(A6,LEN(A6)-FIND("@",SUBSTITUTE(A6," ","@",LEN(A6)-LEN(SUBSTITUTE(A6," ",""))))))
Edit2: Se você quiser usar sua solução -and, você pode usar esta fórmula:
=LEFT(A6,FIND(" ",SUBSTITUTE(A6," and ","-and-")))&RIGHT(A6,LEN(A6)-FIND("@",SUBSTITUTE(A6," ","@",LEN(A6)-LEN(SUBSTITUTE(A6," ","")))))