1

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.

Improve this question

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

You should be able to replace the {"cool", orange"} with the range that contains the words for which you're looking. Then enter the formula as an array formula.
0

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.

Improve this answer

1 Comment

This works thank you! Small note: My version of exel uses ";" to seperate the parts of the formula rather than ','. Might help in case someone else uses this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.