Excel: Test If a Cell Contains Specific Text

Formulas & functions · Updated November 18, 2026

In Excel for Microsoft 365, test whether a cell contains specific text with =ISNUMBER(SEARCH("apple",A2)). It returns TRUE when the text appears anywhere in A2 and FALSE when it does not; SEARCH ignores letter case.

The basic SEARCH formula

SEARCH looks for one piece of text inside another and returns the position where the match begins. ISNUMBER then turns that position into a simple TRUE or FALSE result. If the text is absent, SEARCH returns an error, which ISNUMBER treats as FALSE.

With descriptions in A2:A20 and the word apple as the text to find, enter this in B2 and fill it down:

=ISNUMBER(SEARCH("apple",A2))

For example, if A2 contains “Green apple juice,” the formula returns TRUE because “apple” begins within the cell. If A2 contains “Orange juice,” it returns FALSE. The search can match a word inside a longer word as well: searching for “art” will find a match in “carton.” If you need whole-word matching, see the limitations below.

The formula can also return a label instead of a Boolean value. With the same descriptions in A2:A20, enter this in B2 and fill down:

=IF(ISNUMBER(SEARCH("apple",A2)),"Found","Not found")

That is useful when the result will be read by someone scanning a worksheet or used as a category in a report. You can replace the labels with other outcomes, such as “Review” and “OK.”

Search for text stored in another cell

You do not have to type the search term into the formula. Keeping it in a separate cell makes it easy to change what you are checking without editing the formula each time. For example, put the term in E2 and the descriptions in A2:A20. In B2, enter:

=ISNUMBER(SEARCH($E$2,A2))

Fill the formula down column B. The dollar signs keep the search-term reference fixed at E2 while the reference to A2 changes to A3, A4, and so on. If E2 contains “late,” a description such as “Delivery is late” returns TRUE. If you copy the formula sideways as well as down, the fixed E2 reference remains in place.

A blank search term needs attention: SEARCH treats an empty string as a match at the start of the cell, so the basic formula can return TRUE when E2 is blank. To show a blank result until someone enters a term, use this version in B2, with the same layout:

=IF($E$2="","",IFERROR(ISNUMBER(SEARCH($E$2,A2)),FALSE))

This leaves B2 blank when E2 is empty and returns FALSE if the search encounters an error, such as an error value in A2. IFERROR is useful when the worksheet may contain errors, but it also hides errors that might otherwise reveal a problem in the data.

Case-sensitive and exact-match checks

SEARCH is not case-sensitive, so it finds “apple,” “Apple,” and “APPLE” alike. For a case-sensitive substring check, use FIND instead. With text in A2, this formula returns TRUE only if the exact capitalization “Apple” appears somewhere in the cell:

=ISNUMBER(FIND("Apple",A2))

FIND also returns a character position when it succeeds and an error when it does not, but unlike SEARCH it treats uppercase and lowercase letters as different. FIND does not interpret asterisks or question marks as wildcards. Use it when capitalization matters, not when you want a case-insensitive search.

A substring check is different from checking whether the entire cell equals a value. With the cell to check in A2, use this formula to test whether the whole cell is apple:

=A2="apple"

This comparison is case-insensitive in ordinary Excel text comparisons, but it requires the entire cell value to match; “Green apple” is not equal to “apple.” To require both an exact whole-cell match and matching capitalization, use EXACT:

=EXACT(A2,"apple")

EXACT returns TRUE only when the two text values match, including capitalization. Choose the test based on whether “contains,” “equals,” or “equals with matching case” describes the requirement.

Count cells that contain the text

If you need a count rather than a TRUE/FALSE result for each row, COUNTIF can use asterisks as wildcards. With descriptions in A2:A20, this counts cells containing apple anywhere in their contents:

=COUNTIF(A2:A20,"*apple*")

The asterisk represents any number of characters, including no characters. COUNTIF is not case-sensitive, so this count includes matches with different capitalization. To use a changing search term stored in E2, use:

=COUNTIF(A2:A20,"*"&E2&"*")

If five cells in the range contain the term, the result is 5. This is a compact way to get a total for a dashboard or summary. However, wildcard characters in the search term have special meaning: an asterisk can match any sequence of characters, and a question mark can match one character. For a literal search term that may include those characters, SEARCH is often easier to control; COUNTIF criteria also require care when the text includes wildcard symbols.

Highlight matching cells with conditional formatting

To mark matches visually instead of adding a helper column, select the cells to format—for example, A2:A100—and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter this rule formula, then choose a format such as a fill color:

=ISNUMBER(SEARCH("urgent",A2))

The formula’s A2 reference should correspond to the first cell in the selected range. Excel adjusts the relative reference for each cell, so A3 is checked on the next row. If the range starts at A5, use A5 in the formula instead. Select OK to apply the rule. The formula returns TRUE for a matching cell, which tells conditional formatting to use the chosen style.

For a term in E2, use =ISNUMBER(SEARCH($E$2,A2)) as the rule formula, again making sure A2 is the first cell in the selected range. This lets you change the highlighted term by editing E2. Conditional formatting changes how cells look; it does not replace their contents or create a separate TRUE/FALSE value. The Excel for Mac ribbon has the same general Conditional Formatting route, though the dialog layout can differ. Excel for the web also supports conditional formatting, but its controls may look different from the Windows desktop dialogs.

Choosing the right method

Several approaches can be correct, but they answer slightly different questions. Use the one that matches the desired result:

  • SEARCH with ISNUMBER: Best for a case-insensitive contains test that returns TRUE or FALSE. It works well in helper columns and in conditional-formatting rules.
  • FIND with ISNUMBER: Use for a case-sensitive contains test. It treats the search string literally rather than as a wildcard pattern.
  • COUNTIF with wildcards: Use when you want to count matching cells in a range with a short formula.
  • IF around the test: Use when the result should be a word, number, or blank rather than TRUE or FALSE.
  • Equality or EXACT: Use when the whole cell must match, not merely contain the search term.

For example, a product description that contains a product category calls for a contains test. A product ID that must match a specified ID exactly calls for an equality test. Choosing by meaning prevents a frequent mistake: using a substring formula when extra text should make the cell invalid.

Common problems and edge cases

A TRUE result does not necessarily mean Excel found a separate word. SEARCH looks for a sequence of characters, so “art” matches “carton” and “partial.” Excel’s basic SEARCH and FIND functions do not offer a simple whole-word option. If whole words matter, data structure is often the best fix: store categories in their own cells or normalize the text before searching. A more complex formula may be needed when the text is inconsistent and must be parsed in place.

SEARCH also supports wildcard characters in its search text: an asterisk can represent any number of characters, and a question mark can represent one character. A tilde escapes a wildcard when you need to search for it literally. For instance, to search for the literal text “A*B,” the asterisk must be escaped in the SEARCH text. FIND does not use these wildcards, so it can be simpler for literal, case-sensitive searches.

Another source of confusion is spaces and imported data. A cell may contain a nonbreaking space or other character that looks like an ordinary space but does not match the expected text. If a formula seems wrong, inspect the underlying value, try copying a short section of the cell into a plain-text editor, and check whether the search term itself contains leading or trailing spaces. TRIM can clean ordinary excess spaces, although it does not remove every kind of nonprinting or special whitespace.

Finally, a formula can return an error if the referenced cell already contains an error value. Wrapping a test in IFERROR can make a report cleaner, but consider whether you want to hide that underlying issue. During troubleshooting, removing IFERROR temporarily can help reveal the source of the problem.

Frequently Asked Questions

Is SEARCH case-sensitive in Excel?
No. SEARCH ignores capitalization. Use FIND when the case must match, or EXACT when you need a case-sensitive whole-cell comparison.

How do I return Yes or No instead of TRUE or FALSE?
With the text to check in A2, use =IF(ISNUMBER(SEARCH("apple",A2)),"Yes","No"). Change the two quoted labels to the wording you want.

Can I test whether any of several words are in a cell?
Yes, but the formula depends on how the word list is arranged and whether every matching word or just one is required. For a short list, separate SEARCH tests can be combined with OR; for a longer list, a helper range or a more structured formula is easier to maintain.

Does the formula find a word only when it appears by itself?
No. SEARCH and FIND look for a character sequence, not word boundaries. A search for “art” can match “carton.” Store categories separately or use a word-boundary-aware approach if a substring is not sufficient.

Why does a blank search cell return TRUE?
SEARCH treats an empty search string as a match at the beginning of the text. Add a check that the search cell is not blank, such as the IF-based formula shown above, before relying on the result.

Can I highlight matches without adding a formula column?
Yes. Apply a conditional-formatting rule using a formula such as =ISNUMBER(SEARCH("urgent",A2)) to the target range. Use the range’s first cell in the formula so Excel evaluates each row correctly.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.