# Excel - Delete specific word in each row?

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!

-

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 '10 at 22:28
Your last solution is exactly what I need. Thanks again! – Dave Jul 15 '10 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)
``````

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.

-
Thanks, worked great! – Dave Jul 13 '10 at 21:34
This assumes the first 3 words are 13 characters. – Jarvin Jul 14 '10 at 14:43
@Dan, yes, that's why I said `in your specific case`. – Lance Roberts Jul 14 '10 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 '10 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!

-