Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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!

share|improve this question
add comment

4 Answers

up vote 2 down vote accepted

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," ","")))))
share|improve this answer
    
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
add comment

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.

share|improve this answer
    
Thanks, worked great! –  Dave Jul 13 '10 at 21:34
    
This assumes the first 3 words are 13 characters. –  Dan 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
add comment

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

share|improve this answer
    
Great! Also you can use a function to take the string after the first space character. –  kokbira Jul 14 '10 at 15:55
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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