delete cell that doesn't contain a specific word

L

luke.robert.terry

I'm trying to delete a cell in excel if the content of the cell doesn't
have a specific word. For example if I only wanted cells if it
contained the word 'John' in it.
Then the cell: I love excel - would be deleted but the cell: I love
excel more than John - would not be deleted.

I'm sure it's probably a simple solution but I'm not really sure how to
do this.
Any help would be much appreciated.

Thanks
 
M

Max

Try this on a spare copy of the sheet

Assuming the text cells are all in col A
Select the range, then do a Data > Filter > Autofilter

In the autofilter droplist:
Select Custom, choose "does not contain" under "Show rows where" droplist
Key into the box to the right: John
Click OK

This filters out the cells/rows which does not contain: John
Select the filtered cells, right-click > Delete cells > Delete entire rows >
OK

Remove the autofilter
 
Top