CODEWITHSUNDEEP
excelconditional-formatting
Anne de Groot
Anne de Groot

Reputation: 21

How to apply conditional formatting in excel for multiple text conditions

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.

Upvotes: 1

Views: 248

Answers (2)

teylyn
teylyn

Reputation: 35900

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.

enter image description here

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.

Upvotes: 0

Maximilian Kohl
Maximilian Kohl

Reputation: 640

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.

Upvotes: 2

Related Questions