Excel – Delete specific word in each row

microsoft excelworksheet-function

Is there a way to have Excel delete a sepcific word in every row? For example, each row says Mr. and Mrs. {first name} {last name}, and I want to delete the {first name} from each one. (Or alternatively, by extracting the first three words and the last word, and then combining them.)

Thanks!

Best Answer

Here is my solution. Which uses in part this.

=LEFT(A6,FIND(" ",A6,FIND(" ",A6,FIND(" ",A6)+1)+1))&RIGHT(A6,LEN(A6)-FIND("@",SUBSTITUTE(A6," ","@",LEN(A6)-LEN(SUBSTITUTE(A6," ","")))))

This keeps the first 3 words and the last word and doesn't need to assume the first three words are "Mr. and Mrs.". If you do assume that you can use the shorter version:

="Mr. and Mrs. "&RIGHT(A6,LEN(A6)-FIND("@",SUBSTITUTE(A6," ","@",LEN(A6)-LEN(SUBSTITUTE(A6," ","")))))

Edit: using my top formula and this one (which just gives the first and last word):

=LEFT(A6,FIND(" ",A6))&RIGHT(A6,LEN(A6)-FIND("@",SUBSTITUTE(A6," ","@",LEN(A6)-LEN(SUBSTITUTE(A6," ","")))))

We can combine those with an if statement... I'm not sure what if statement would be best, but let me show you what one would look like where we count the number of "." in the statement. If it is 2 or greater, use the first 3 words and last, otherwise just the first word and the last.

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: If you want to use your -and- solution you can use this formula:

=LEFT(A6,FIND(" ",SUBSTITUTE(A6," and ","-and-")))&RIGHT(A6,LEN(A6)-FIND("@",SUBSTITUTE(A6," ","@",LEN(A6)-LEN(SUBSTITUTE(A6," ","")))))
Related Question