Cell Contains Some Words But Not Others?

This article explains how to check whether a cell contains some words but not others, a common spreadsheet task for filtering, categorizing, and data validation. You will learn the exact formulas for Excel and Google Sheets, how to handle case sensitivity, and how to avoid the most frequent mistakes.

Formula

The core formula to test if a cell contains one set of words but not another uses the SEARCH (or FIND) function combined with ISNUMBER and NOT. In plain terms:

Contains any of the “include” words AND none of the “exclude” words.

For a single include word and a single exclude word, the formula is:

=AND(ISNUMBER(SEARCH("include", A1)), NOT(ISNUMBER(SEARCH("exclude", A1))))

For multiple include words (any of them) and multiple exclude words (none of them), use SUMPRODUCT:

=AND(SUMPRODUCT(--ISNUMBER(SEARCH({"word1","word2"}, A1)))>0, SUMPRODUCT(--ISNUMBER(SEARCH({"exclude1","exclude2"}, A1)))=0)

Variables:

• A1 – the cell being tested.
• “include” – the word(s) that must be present.
• “exclude” – the word(s) that must be absent.
• SEARCH – finds a text string within another, ignoring case; returns the position number if found, or an error if not.
• ISNUMBER – converts the result to TRUE if a number (i.e., the word was found), FALSE if an error.

Worked Example

Using the default values from the calculator above is not relevant here, so let’s use a spreadsheet example. Suppose cell A1 contains the text: “Red apple pie”.

You want to check if it contains “apple” but not “green”. The formula is:

=AND(ISNUMBER(SEARCH("apple", A1)), NOT(ISNUMBER(SEARCH("green", A1))))

Step-by-step:

1. SEARCH("apple", A1) returns 5 (the position where “apple” starts).
2. ISNUMBER(5) returns TRUE.
3. SEARCH("green", A1) returns #VALUE! (not found).
4. ISNUMBER(#VALUE!) returns FALSE.
5. NOT(FALSE) returns TRUE.
6. AND(TRUE, TRUE) returns TRUE.

The result is TRUE, which matches the condition.

When to Use This Pattern

This formula is useful for:

• Filtering product lists by keyword inclusion and exclusion (e.g., show rows with “wireless” but not “charger”).
• Validating survey responses that mention certain topics but not others.
• Cleaning text data where you need to categorize based on multiple conditions.
• Building dynamic dashboards where you want to flag rows that meet complex text criteria.

It works in both Excel and Google Sheets, though the array constants (the curly braces) require you to press Ctrl+Shift+Enter in older Excel versions (pre-365). In Excel 365 and Google Sheets, it works normally.

How to Choose Between SEARCH and FIND

The main difference is case sensitivity:

• SEARCH ignores case: “Apple” matches “apple”, “APPLE”, etc.
• FIND is case-sensitive: “Apple” only matches “Apple” exactly.

If you need case-sensitive matching, replace SEARCH with FIND in the formulas above. For example, to find “Mac” but not “mac” (as in Macintosh vs. mac and cheese), use FIND.

Common Mistakes and How to Avoid Them

Several pitfalls trip up users:

• Using COUNTIF instead of SUMPRODUCT for multiple words: COUNTIF with wildcards works for a single word, but combining multiple include/exclude conditions becomes messy. The SUMPRODUCT approach is more scalable.
• Forgetting that SEARCH returns an error when not found: Wrapping with ISNUMBER is essential; otherwise the formula returns #VALUE!.
• Partial matches are the default: SEARCH finds substrings, so “cat” matches “catalog”. If you need whole-word matches, you must add spaces or word boundaries manually (e.g., search for ” cat ” but that fails at the start/end). A more robust method is to use a regular expression via REGEXMATCH in Google Sheets, or a UDF in Excel.
• Array constants in older Excel: If you use curly braces and get a #VALUE! error, press Ctrl+Shift+Enter to enter it as an array formula.

Alternative Approaches

Depending on your needs, other methods may be simpler:

• FILTER function (Excel 365 / Google Sheets): Use =FILTER(A:A, ISNUMBER(SEARCH("include", A:A)) * NOT(ISNUMBER(SEARCH("exclude", A:A)))) to return all matching rows.
• Power Query (Excel): Use the “Contains” text filter in the query editor for a point-and-click solution.
• Conditional Formatting: Highlight cells that meet the criteria using a formula rule, which is useful for visual scanning.
• REGEXMATCH (Google Sheets): For complex patterns, use =REGEXMATCH(A1, "^(?=.*include)(?!.*exclude)") for whole-word or advanced matching.

Frequently Asked Questions

How do I check if a cell contains any of several words but not any of several other words?

Use the SUMPRODUCT formula shown above. Replace the curly-brace arrays with your lists of include and exclude words. The formula counts how many include words are found and how many exclude words are found; you require the first count to be greater than zero and the second to be zero.

Does this work in Google Sheets the same way as Excel?

Yes, the SEARCH, FIND, ISNUMBER, and SUMPRODUCT functions work identically in Google Sheets. The only difference is that you don’t need to press Ctrl+Shift+Enter for array constants in Google Sheets; it handles them automatically.

Can I use wildcards like * or ? in the SEARCH function?

No, SEARCH does not support wildcards. If you need wildcard matching, use COUNTIF with wildcards (e.g., =COUNTIF(A1, "*apple*")), but that only works for a single condition. For multiple conditions, consider using REGEXMATCH in Google Sheets or a custom function in Excel.

How do I make the search case-sensitive?

Replace SEARCH with FIND in the formula. For example, =AND(ISNUMBER(FIND("Mac", A1)), NOT(ISNUMBER(FIND("mac", A1)))) will only match “Mac” with a capital M.

Why does my formula return #VALUE! instead of TRUE or FALSE?

This usually happens because you are using the SUMPRODUCT version and forgot to press Ctrl+Shift+Enter in older Excel, or because you have a typo in the array constants. Double-check that the curly braces are correct and that you are not mixing commas and semicolons (depending on your regional settings, the list separator might be a semicolon).

Is there a way to do this without a formula, like using a filter?

Yes, you can use Excel’s built-in Text Filters (contains, does not contain) on a table, but that only allows two conditions at a time. For more complex logic, a helper column with the formula is the most flexible approach.

Related

• Samsung SRF9400BFH: Buy for Capacity, Not Screen
• UPI vs Cards: Merchant Fees & Customer Choice
• VLC Record Screen With Audio: Full Guide

in t | Total time (min) | min | 50 | 0
in d | Distance (km) | km | 10 | 0
out pace | Average pace (min/km) | min/km | t / d | 2
out speed | Average speed (km/h) | km/h | d / (t / 60) | 2