Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this community
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," ","")))))
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.
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
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!
