ThisCalculator

Everyday

Cell Contains Specific Text? Formula & Examples

Updated · Standard formula, shown below

Cell Contains Specific Text? Formula & Examples

This guide explains how to check if a cell contains specific text in spreadsheet applications like Excel and Google Sheets, and what formula to use. You will learn the exact formulas for partial matches, case-sensitive checks, and how to return custom results, with clear examples you can copy.

Why You Need a “Contains Text” Formula

Spreadsheets store data, but the real work is in categorizing, cleaning, and acting on that data. A common task is to test whether a cell contains a certain word or phrase, then do something based on that result. For example, you might want to flag all rows where the product name contains “Pro”, or check if an email address contains a specific domain. Excel and Google Sheets do not have a single “CONTAINS” function, so you need to combine existing functions like ISNUMBER, SEARCH, and IF to build the test. This article covers the standard formulas that work in both Excel and Google Sheets, with minor syntax differences noted.

The Basic Formula: ISNUMBER + SEARCH

The most reliable way to check for a partial text match is to use the SEARCH function inside ISNUMBER. SEARCH looks for a substring within a text string and returns the position where it starts, or an error if it is not found. ISNUMBER then converts that into a simple TRUE or FALSE.

The generic formula is:

=ISNUMBER(SEARCH("text", A1))

This returns TRUE if cell A1 contains the word “text” anywhere, and FALSE if it does not. For example, if A1 contains “Project Alpha”, the formula =ISNUMBER(SEARCH("ject", A1)) returns TRUE because “ject” is found at position 3.

To return a custom message instead of TRUE/FALSE, wrap it in an IF function:

=IF(ISNUMBER(SEARCH("text", A1)), "Yes", "No")

Case-Sensitive Search: FIND instead of SEARCH

One important difference is that SEARCH is case-insensitive, meaning it treats “TEXT” and “text” as the same. If you need a case-sensitive match, use FIND instead. FIND works exactly the same way but respects upper and lower case.

For a case-sensitive check, the formula becomes:

=ISNUMBER(FIND("text", A1))

So if A1 contains “Text”, =ISNUMBER(FIND("text", A1)) returns FALSE, because the lowercase “t” is not found. This is useful when you need to distinguish between codes like “abc” and “ABC”.

How to Use the Formula with Other Functions

The basic formula becomes powerful when combined with other functions. Here are common variations:

  • Count cells that contain specific text: Use =COUNTIF(A1:A10, "*text*") to count how many cells in a range contain the word “text”. The asterisks are wildcards that match any number of characters.
  • Sum values based on a text match: Use =SUMIF(B1:B10, "*text*", C1:C10) to sum values in column C where the corresponding cell in column B contains “text”.
  • Return a different value if the cell does not contain the text: The IF function can handle this. For example, =IF(ISNUMBER(SEARCH("text", A1)), "Found", "Not Found").
  • Check if a cell starts with specific text: Use =IF(LEFT(A1,4)="text", "Yes", "No") to check if the first four characters are exactly “text”.

Common Mistakes and How to Avoid Them

Several pitfalls trip up users when working with text matching:

  • Using the wrong function: SEARCH and FIND return a number (position) or an error. If you use them alone, you get a number like 1 or an error like #VALUE!. Always wrap them in ISNUMBER to get TRUE/FALSE.
  • Forgetting wildcards in COUNTIF: COUNTIF(A1:A10, "text") only counts cells that are exactly equal to “text”, not cells that contain it. You must use "*text*" for a partial match.
  • Mixing up SEARCH and FIND: As noted, SEARCH ignores case, FIND does not. Choose based on your need.
  • Hardcoding text with quotes: In formulas, text must be enclosed in double quotes. A common error is writing SEARCH(text, A1) without quotes, which Excel interprets as a named range or function.

Google Sheets vs. Excel: Differences

The formulas work identically in both Excel and Google Sheets for the basic ISNUMBER(SEARCH()) pattern. However, there are a few minor differences:

  • Wildcards in COUNTIF: Excel supports wildcards (* and ?) in COUNTIF/SUMIF. Google Sheets also supports them, but only when the criteria is a string. In Google Sheets, you can also use regular expressions with functions like REGEXMATCH for more complex patterns.
  • Case-insensitive by default: Both Excel and Google Sheets treat SEARCH as case-insensitive. For case-sensitive, use FIND in both.
  • Array formulas: In older Excel versions, you might need to enter array formulas with Ctrl+Shift+Enter, but modern Excel and Google Sheets handle them automatically.

For most tasks, you can copy the same formula between the two without any changes.

Frequently Asked Questions

How do I check if a cell contains one of several specific texts?

You can nest multiple ISNUMBER(SEARCH()) tests inside an OR function. For example, to check if A1 contains “apple” OR “orange”, use =IF(OR(ISNUMBER(SEARCH("apple", A1)), ISNUMBER(SEARCH("orange", A1))), "Yes", "No"). For many values, consider using an array formula or a helper column.

Can I use a cell reference for the text I’m looking for?

Yes, instead of typing the text in quotes, you can reference a cell that contains the text. For example, =ISNUMBER(SEARCH(B1, A1)) will check if A1 contains whatever text is in B1. This is useful for dynamic criteria.

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

This happens when you use SEARCH or FIND without wrapping them in ISNUMBER. The function returns an error when the text is not found, and that error propagates. Always use =ISNUMBER(SEARCH(...)) to get a clean TRUE/FALSE result.

How do I check if a cell contains text but not specific text?

You can combine conditions with AND and NOT. For example, to check if A1 contains “text” but does NOT contain “special”, use =IF(AND(ISNUMBER(SEARCH("text", A1)), NOT(ISNUMBER(SEARCH("special", A1)))), "Yes", "No").

Does the formula work with numbers and dates?

Yes, but with a caveat. SEARCH and FIND work on text strings. If a cell contains a number, Excel and Google Sheets will automatically convert it to text for the function. However, be careful with dates, as they may be stored as serial numbers. It is often better to use TEXT(A1, "format") to convert dates to a readable string first.

Can I highlight cells that contain specific text using conditional formatting?

Yes, you can use the same logic in conditional formatting. Select the range, go to Conditional Formatting > New Rule > Use a formula, and enter =ISNUMBER(SEARCH("text", A1)) (adjusting A1 to the top-left cell of your selection). This will highlight all cells that contain the text.

Limitations and Performance Considerations

These formulas are simple and fast for most datasets. However, if you are working with tens of thousands of rows, using ISNUMBER(SEARCH()) in a column can slow down recalculation. In such cases, consider using Power Query (Excel) or the built-in Find & Replace feature for one-off checks. Also, note that SEARCH and FIND are not case-sensitive by default in SEARCH; if you need case-sensitive, use FIND, but remember that FIND does not support wildcards. For complex pattern matching, regular expressions in Google Sheets (REGEXMATCH) or Excel’s FILTERXML (in older versions) can be more flexible but are harder to learn.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.