i need to ask if you there is something in excel where you can find a specific word in a cell and delete that but keep the other words or character in place. For example if I just want to delete dog in a cell. But the dog can be found in a middle of the word, at the end or the beginning. I just want to find that word and delete that
Excel – How to delete a specific word in Excel
microsoft excel
Related Solutions
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," ","")))))
In case anyone else runs into this, I was able to synthesize a solution from the updates and other comments above. When you paste the data from Excel into Word, choose Paste > Paste Special..., then choose the Paste Link radio button, and Unformatted Unicode Text. Looks like this in Office 2016:
If your content actually has some formatting, one of the other options might be better. In my case I just wanted the plain text content, which I was then able to style in Word to match its context.
Best Answer
Press Ctrl+F and select the replace tab. This will find and replace words in the document.
Find Replace Window shown here: