How to apply conditional formatting in excel for multiple text conditions

Question

I am trying to delete a large number of cases (Tweets) in excel based on certain words. Only one word has to be present for me to delete it.

example:

1. blue big bird
2. orange bird flies
3. elephant is angry
4. cool cat in tree

List of words I would want to delete on: bird, blue and cat. Therefore, the function should delete 1. 2. and 4, no matter if all words are present, or only one or two. Currently I only know how to format it based on one word, but I have roughly 50 words per file to filter on, so it would save a lot of time to have a function. I am not sure which function works for this? I already have a list of the words I want to delete on in another spreadsheet.

Answer 1

The formula you are searching is

=IF(SUM(COUNTIF(B2,"*"&{"cool","orange"}&"*"))>0,B2,"")

where B2 is a cell in the row with your values (e.g. 1. blue big bird). Apply this for every cell and you get the cell value for every hit and "" for no hit.

Answer 2

If you already have a list of words in a spreadsheet, you can assign that list a range name, since 50 words are a bit much for a formula and hard to maintain.

Consider the following screenshot. The highlighted range has the range name TriggerWords.

The formula in cell B1 is

=IF(SUM(COUNTIF(A1,"*"&TriggerWords&"*"))>0,"",A1)

which is an array formula that must be confirmed with Ctrl-Shift-Enter. Then copy down.