Excel CONTAINS: How to Check If a Cell Contains Text

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

To check whether a cell contains a given piece of text in Excel, combine ISNUMBER with SEARCH. SEARCH returns the position of the substring inside the cell, and ISNUMBER converts that position into TRUE or FALSE. This is the standard "contains excel" test, and it works for any substring, not just whole-cell matches.

Quick Answer

• =ISNUMBER(SEARCH("text",A2)) returns TRUE if A2 contains "text" anywhere, and FALSE if it does not.
• SEARCH is case-insensitive, so "gmail" matches "Gmail" and "GMAIL".
• ISNUMBER is needed because SEARCH returns a number when it finds a match and an error when it does not.
• Wrap the test in IF to get a readable label: =IF(ISNUMBER(SEARCH("gmail",A2)),"Yes","No").
• Use COUNTIF with a wildcard when you only need a count of matching cells, not a per-row flag.

Syntax

The core test uses two functions nested together. Here are the arguments for each.

The full pattern is:

$$=ISNUMBER(SEARCH("substring", cell))$$

For a count across a range, the pattern is:

$$=COUNTIF(range, "substring")$$

How It Works

SEARCH scans the within_text string from left to right and returns the character position where find_text first appears. If you search for "gmail" in [email protected], the match starts at position 11, so SEARCH returns 11. If the substring is not present at all, SEARCH returns the #VALUE! error instead of a number.

That error is the reason ISNUMBER sits on the outside. ISNUMBER looks at whatever SEARCH produced. A position number is a number, so ISNUMBER returns TRUE. An error is not a number, so ISNUMBER returns FALSE. The nesting turns a position-or-error result into a clean logical value you can filter, count or feed into IF.

Two behaviors are worth remembering. First, SEARCH ignores case, so it treats uppercase and lowercase letters as equal. Second, SEARCH supports the wildcard characters ? (any single character) and * (any sequence of characters) inside find_text. If you need a case-sensitive test, use FIND instead, which has the same argument structure but distinguishes uppercase from lowercase. The Excel FIND function guide covers that comparison in detail.

Worked Example

The table below holds a list of email addresses in column A. Column B tests each address for the substring "gmail", and column C turns that test into a Yes/No label.

Column B uses ISNUMBER(SEARCH("gmail",A2)) to test each email for the substring, and column C converts the TRUE/FALSE result into Yes/No.

The formula in B2 returns TRUE because "gmail" appears inside [email protected]. Copying it down gives FALSE for the Outlook, Yahoo, Hotmail and ProtonMail addresses, and TRUE for the three remaining Gmail addresses. Column C then reads the boolean in column B and prints "Yes" or "No", which is easier to scan and to sort.

More Examples

Case-insensitive match. Because SEARCH ignores case, =ISNUMBER(SEARCH("GMAIL",A2)) returns the same TRUE as the lowercase version. You do not need to normalize the case of your data first.

Case-sensitive match. Swap SEARCH for FIND: =ISNUMBER(FIND("Gmail",A2)). This returns TRUE only when the capital G and lowercase rest match exactly.

Count matching cells. If you only need a total, skip the helper column and use a wildcard count:

=COUNTIF(A2:A9,"*gmail*")   // returns 4

The asterisks on both sides mean "any characters before and after gmail". The COUNTIF cell contains text tutorial walks through the wildcard rules and the differences between COUNTIF and SUMPRODUCT counting.

Filter rows. Feed the boolean into a filter or a helper column and keep only the TRUE rows. This is the same idea as filtering a table by a text pattern in code, which the pandas str.contains guide covers for Python users.

Return a custom label. Replace "Yes" and "No" with anything you like: =IF(ISNUMBER(SEARCH("gmail",A2)),"Google","Other"). The Excel IF cell contains text article shows more label and wildcard variations.

Test for several substrings. Nest OR around two searches to match either term: =IF(OR(ISNUMBER(SEARCH("gmail",A2)),ISNUMBER(SEARCH("yahoo",A2))),"Match","No match"). For longer lists of conditions, the IFS function keeps the formula readable.

Errors and How to Fix Them

Common Mistakes

• Using SEARCH alone. =SEARCH("gmail",A2) returns a number or an error, not TRUE/FALSE. Always wrap it in ISNUMBER when you want a logical test.
• Expecting a whole-cell match. SEARCH finds the substring anywhere in the cell. "gmail" matches [email protected] too. Anchor the test with EXACT or compare the full string if you need an exact match.
• Forgetting case sensitivity. SEARCH is case-insensitive by design. If case matters, use FIND instead of SEARCH.
• Leaving wildcards unescaped. A literal * or ? inside find_text is treated as a wildcard. Prefix it with ~ to search for the character itself.
• Hardcoding the substring in every row. Put the search term in its own cell and reference it, for example SEARCH($E$1,A2), so you can change it once.
• Ignoring empty cells. An empty cell makes SEARCH return an error, which ISNUMBER turns into FALSE. That is usually correct, but check it if blanks should be treated differently.

Limitations

The ISNUMBER(SEARCH(...)) pattern tells you only whether a substring exists somewhere in the cell. It cannot tell you how many times it appears, where each occurrence sits, or whether the match is a whole word. Searching for "mail" will match "gmail", "mailbox" and "email" alike, so short substrings produce false positives.

The test also runs on one cell at a time. To count or aggregate across a range you need COUNTIF, SUMPRODUCT or a helper column, and those approaches have their own wildcard and case rules. SEARCH also cannot handle regular expressions, so complex patterns such as "starts with a digit and ends with .com" require a different approach.

Frequently Asked Questions

How do I check if a cell contains specific text in Excel?

Use =ISNUMBER(SEARCH("text",A2)). SEARCH returns the position of the substring, and ISNUMBER converts that into TRUE or FALSE. If you want a word instead of a boolean, wrap the whole thing in IF.

Is there a CONTAINS function in Excel?

No. Excel has no CONTAINS function. The standard replacement is ISNUMBER combined with SEARCH or FIND. For counting, COUNTIF with wildcards gives you a contains-style test across a range.

What is the difference between SEARCH and FIND?

Both return the position of a substring. SEARCH is case-insensitive and allows the wildcards ? and *. FIND is case-sensitive and treats wildcards as ordinary characters. Choose FIND when capitalization matters.

Why does my contains formula return #VALUE!?

SEARCH returns #VALUE! when the substring is not found or the cell is empty. If you see the error on its own, you forgot to wrap the search in ISNUMBER. With ISNUMBER on the outside, the same situation returns FALSE instead.

Can I count how many cells contain a certain text?

Yes. Use =COUNTIF(A2:A9,"gmail"), which counts cells containing "gmail" anywhere. The asterisks are wildcards that stand for any characters before and after the search term. For case-sensitive counting, combine SUMPRODUCT with ISNUMBER and FIND.

References

This article draws on the standard references listed under Further Reading.

Further Reading

• Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician
• Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology
• Microsoft Support: Excel help and learning
• McCullough B, Heiser DA (2008). On the accuracy of statistical procedures in Microsoft Excel 2007. Computational Statistics & Data Analysis
• Abeysooriya M, Soria M, Kasu MS et al. (2021). Gene name errors: Lessons not learned. PLOS Computational Biology
• Wilson G, Bryan J, Cranston K et al. (2017). Good enough practices in scientific computing. PLOS Computational Biology

Related Articles

• Excel IF Cell Contains Text: Formula Examples and Wildcards
• COUNTIF Cell Contains Text in Excel: Formula and Examples
• pandas str.contains: Filter Rows by Text in Python
• How to Count Cells With Text in Excel (Step by Step)
• Excel IS Functions: ISNUMBER, ISBLANK and More