OmniDocCheck if a Cell Does Not Contain Text in Excel
Build a does-not-contain text formula in Excel with NOT, ISNUMBER and SEARCH, handle blanks and case, and exclude several words at once.
If you clean up a long list in Excel, you will eventually need to flag or remove rows that do not contain a certain word, code or phrase. Maybe you want to keep every invoice number that does not start with a draft tag, or drop any customer note that does not mention a refund. Searching for "does not contain text excel formula" usually leads to pages that cover IF in general, which leaves you to work out the negation yourself.
The short answer is a formula that searches for the text, turns the search result into TRUE or FALSE, and then reverses it. This article shows that formula, how to change it for case sensitivity and blank cells, and how to exclude several words at once. Everything here works in standard desktop Excel and in Excel for the web, and you can copy the formulas into any spreadsheet that supports the same functions.
How do you write an Excel formula that does not contain specific text? The short answer
Use NOT with ISNUMBER and SEARCH. The formula returns TRUE when the cell does not contain the text and FALSE when it does. Wrap the result in IF if you want labels such as "Keep" or "Drop" instead of TRUE and FALSE.
| Your situation | What to do | Why |
|---|---|---|
| You want TRUE or FALSE for each row | Use =NOT(ISNUMBER(SEARCH(text, cell))) | SEARCH returns a position when it finds the text and an error when it does not; ISNUMBER converts that into TRUE or FALSE, and NOT reverses it. |
| You want readable labels | Use =IF(ISNUMBER(SEARCH(text, cell)), "Contains", "Does not contain") | The IF function turns the result into words you can sort and filter. |
| The match must respect capital letters | Replace SEARCH with FIND | FIND is case-sensitive, so "Draft" and "draft" are treated as different text. |
| You need to exclude several words | Compare SEARCH against an array of words and count the matches | One formula can check every word in the list without nesting several IF functions. |
| You only need to hide rows, not build a column | Use the text filters on the column | Excel can filter out rows that contain a value without any formula. |
Before you start
Check these points first. Each one changes what the formula returns.
- Where the text comes from. Decide whether the search word will be typed into the formula or stored in a cell. A cell reference is easier to change later and avoids repeating the same word in every formula.
- Whether capital letters matter. SEARCH ignores case by default. If "ABC" and "abc" should count as different, you need FIND instead.
- Whether blank cells should count. A blank cell does not contain the text, so the formula returns TRUE for it. Decide whether blanks belong in your results before you copy the formula down a column.
- Whether the data is text or numbers. SEARCH works on numbers too, because it converts them to text first. A search for "1" will match "2019" and "10", so use a more specific string when you are looking for digits.
- Whether the text contains wildcard characters. SEARCH treats a question mark as "any one character" and an asterisk as "any run of characters." To search for a literal question mark, type a tilde before it (~?).
Building the does-not-contain formula in Excel, step by step
- Type the search text in a cell. Put the word you want to exclude, such as "draft," in an empty cell like F1. This keeps the formula flexible, because you can change the word without editing every formula.
- Pick the first cell for the result. Click the cell next to the first value you want to check. If your data starts in A2, choose B2.
- Enter the negated search. Type =NOT(ISNUMBER(SEARCH($F$1,A2))) and press Enter. The dollar signs lock the reference to F1 so the formula still points there when you copy it down.
- Read the result. TRUE means the cell in A2 does not contain the word in F1. FALSE means it does.
- Make the output easier to scan. If you want labels, use =IF(ISNUMBER(SEARCH($F$1,A2)),"Contains","Does not contain"). The labels are clearer when you share the sheet with someone else.
- Switch to a case-sensitive check if needed. Replace SEARCH with FIND in the same formula, so it reads =NOT(ISNUMBER(FIND($F$1,A2))). Only this version separates "Draft" from "draft."
- Exclude several words at once. Put the words in a list and count the matches. For example, =SUMPRODUCT(--ISNUMBER(SEARCH({"draft","old","test"},A2)))=0 returns TRUE only when none of the three words appears in A2. The curly braces create the list inside the formula.
- Fill the formula down the column. Double-click the fill handle at the bottom-right corner of the cell, or drag it down to the last row of your data. Check the first few results before you use the column for sorting or filtering.
Check the result
Test the formula on a few rows you already understand before you trust it on the whole sheet.
| Check | How to see it | If it's wrong |
|---|---|---|
| The word is found where you expect | Find a row that clearly contains the word and confirm the formula returns FALSE or "Contains" for it | Confirm the search word in F1 has no extra spaces, and that the reference in the formula points to the right cell. |
| Case behaves as intended | Test a row that differs only in capitalization | If it should be case-sensitive, switch from SEARCH to FIND. |
| Blank cells are handled correctly | Look at an empty row in your data | Add a condition such as AND(A2<>"", ...) so blanks show as blank instead of TRUE. |
| Partial matches are acceptable | Look for a word that appears inside a longer word, such as "draft" inside "drafted" | Add spaces around the search word, or use a more specific phrase. |
| The count makes sense | Filter for TRUE and count the rows, then compare with a manual count of a small sample | Check whether wildcard characters in the search text are changing the match. |
If you filter the results, remember that filtering hides rows without deleting them. Clear the filter before you copy or delete data, so you do not act on a partial view by mistake.
📝 Summary
The formula for a does-not-contain check in Excel is NOT wrapped around ISNUMBER and SEARCH, with the search word stored in its own cell. Switch to FIND for case-sensitive matches, decide how blank cells should be treated, and use an array of words when you need to exclude several terms at once.
❓ FAQ
1. Why does my formula return an error instead of TRUE or FALSE?
The error usually comes from a reference that points to the wrong cell or from a formula that was typed with a missing bracket. Check that the search word cell and the data cell both exist and that the brackets match. If the formula works in one row but not another, that row may contain an error value, which you can handle by wrapping the formula in IFERROR.
2. Is there a difference between SEARCH and FIND for this?
Yes. SEARCH ignores capital letters and allows wildcards, while FIND is case-sensitive and treats wildcard characters as ordinary text. Use SEARCH for everyday matching and FIND when the capitalization or a literal asterisk or question mark matters.
3. Can I use COUNTIF instead?
COUNTIF can do a similar job with a wildcard pattern, such as =COUNTIF(A2,"draft")=0. It is not case-sensitive, and it can behave differently with very long text, so test it on your data before relying on it.
4. How do I exclude rows without writing a formula?
Use the text filter options on the column header. Choose the option for not containing text, type the word, and apply the filter. This hides matching rows without changing the data, which works well for a quick review, but the result will not update when the source text changes the way a formula will.
