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.)



5 Answers 5


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," ","")))))
  • Wow, that's great. I was wondering how to deal with instances where there is only one title (e.g., only "Mrs."). My thought was to temporarily substitute " and " with "-and-", so that all titles are treated as a single word; then keep the first and last words; and finally, switch "-and-" back to " and ". Presumably there is a way to incorporate this into the formula, but I don't even know where to start. Thanks!
    – Dave
    Jul 13, 2010 at 22:28
  • Your last solution is exactly what I need. Thanks again!
    – Dave
    Jul 15, 2010 at 14:15

Here is the formula for finding the third word, but it's a long one:

=MID(MID(MID(SUBSTITUTE(A1," ","^",3),1,256),FIND("^",SUBSTITUTE(A1," ","^",3)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A1," ","^",3),1,256),FIND("^",SUBSTITUTE(A1," ","^",3)),256))-2)

Reference Link here.

Now, put this in one column, click and drag, then create a second column that removes this word.

Actually, in your specific case, you can just use this formula, in this example, A1 has the string and C1 is where the formula above is:

=LEFT(A1, 13) & RIGHT(A1,LEN(A1) - (13 + LEN(C1)))

And, of course, you could substitute the entire first formula for C1, to get it all in one formula.

  • This assumes the first 3 words are 13 characters.
    – Jarvin
    Jul 14, 2010 at 14:43
  • @Dan, yes, that's why I said in your specific case. Jul 14, 2010 at 15:20

all the solutions above are amazing; however, a nice way of doing this (Assumint Mr. John Smith is in one column) is to do tools to columns from the Data menu and use space as a delimiter

  • Great! Also you can use a function to take the string after the first space character.
    – kokbira
    Jul 14, 2010 at 15:55

Ctrl+F, choose replace, put "Mr. " (do not forget the space character) and in replace with must be empty. Then replace all.
Ctrl+F, choose replace, put "Mrs. " (do not forget the space character) and in replace with must be empty. Then replace all.

That solution is fast for few words to replace, but must be used only with words that are not inside a name (with "James Amr. Kohn" for example; in this case, pehaps "case sensitive" option solves it).


I'd use the Ctr+F and use the replace tool, simply type the word you want to remove then click replace - this will replace your chosen word with nothing!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.