# Excel CONTAINS: How to Check If a Cell Contains Text

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.

| Function | Argument | Required? | Meaning |
|---|---|---|---|
| `SEARCH` | `find_text` | Yes | The substring you are looking for, such as `"gmail"`. |
| `SEARCH` | `within_text` | Yes | The cell or text you are searching inside, such as `A2`. |
| `SEARCH` | `start_num` | No | The character position to start searching from. Defaults to 1. |
| `ISNUMBER` | `value` | Yes | The value to test. Returns `TRUE` if it is a number, `FALSE` otherwise. |
| `IF` | `logical_test` | Yes | The condition, usually the `ISNUMBER(SEARCH(...))` result. |
| `IF` | `value_if_true` | Yes | What to return when the test is `TRUE`. |
| `IF` | `value_if_false` | Yes | What to return when the test is `FALSE`. |

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 `ana.silva@gmail.com`, 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](/blog/data-analysis/excel-find-function-syntax-examples) 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.

| Row | A | B | C |
|---|---|---|---|
| 1 | Email | Contains gmail | Gmail User |
| 2 | ana.silva@gmail.com | `=ISNUMBER(SEARCH("gmail",A2))` -> displays TRUE | `=IF(B2,"Yes","No")` -> displays Yes |
| 3 | bruno.costa@outlook.com | `=ISNUMBER(SEARCH("gmail",A3))` -> displays FALSE | `=IF(B3,"Yes","No")` -> displays No |
| 4 | carla.mendes@gmail.com | `=ISNUMBER(SEARCH("gmail",A4))` -> displays TRUE | `=IF(B4,"Yes","No")` -> displays Yes |
| 5 | diego.rocha@yahoo.com | `=ISNUMBER(SEARCH("gmail",A5))` -> displays FALSE | `=IF(B5,"Yes","No")` -> displays No |
| 6 | elisa.nunes@gmail.com | `=ISNUMBER(SEARCH("gmail",A6))` -> displays TRUE | `=IF(B6,"Yes","No")` -> displays Yes |
| 7 | felipe.alves@hotmail.com | `=ISNUMBER(SEARCH("gmail",A7))` -> displays FALSE | `=IF(B7,"Yes","No")` -> displays No |
| 8 | gabriela.ferreira@gmail.com | `=ISNUMBER(SEARCH("gmail",A8))` -> displays TRUE | `=IF(B8,"Yes","No")` -> displays Yes |
| 9 | henrique.lima@protonmail.com | `=ISNUMBER(SEARCH("gmail",A9))` -> displays FALSE | `=IF(B9,"Yes","No")` -> displays No |

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 `ana.silva@gmail.com`. 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](/blog/data-analysis/countif-cell-contains-text) 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](/blog/data-analysis/pandas-str-contains) 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](/blog/data-analysis/excel-if-cell-contains-text) 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](/blog/data-analysis/ifs-function-excel) keeps the formula readable.

## Errors and How to Fix Them

| Error | Cause | Fix |
|---|---|---|
| `#VALUE!` | `SEARCH` did not find the substring, or the cell is empty. | Wrap the search in `ISNUMBER` so the error becomes `FALSE`. |
| `#NAME?` | The function name is misspelled, for example `SEARCHH`. | Check the spelling and that the argument parentheses are balanced. |
| Unexpected `FALSE` on a number or date | SEARCH reads the stored value, not the displayed format, so a date or currency cell is searched as its underlying number. | Search the formatted text with `TEXT`, for example `SEARCH("Jan",TEXT(A2,"mmm d"))`. |
| Wrong result from `*` or `?` | The substring itself contains a wildcard character. | Escape it with a tilde, for example `"~*"` to search for a literal asterisk. |
| `FALSE` when you expected `TRUE` | Leading or trailing spaces in the cell. | Clean the text with `TRIM` before searching. |

## 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 `notgmail@example.com` 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](https://doi.org/10.1080/00031305.2017.1375989)
- [Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology](https://doi.org/10.1186/s13059-016-1044-7)
- [Microsoft Support: Excel help and learning](https://support.microsoft.com/en-us/excel)
- [McCullough B, Heiser DA (2008). On the accuracy of statistical procedures in Microsoft Excel 2007. Computational Statistics & Data Analysis](https://doi.org/10.1016/j.csda.2008.03.004)
- [Abeysooriya M, Soria M, Kasu MS et al. (2021). Gene name errors: Lessons not learned. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1008984)
- [Wilson G, Bryan J, Cranston K et al. (2017). Good enough practices in scientific computing. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1005510)

## Related Articles

- [Excel IF Cell Contains Text: Formula Examples and Wildcards](/blog/data-analysis/excel-if-cell-contains-text)
- [COUNTIF Cell Contains Text in Excel: Formula and Examples](/blog/data-analysis/countif-cell-contains-text)
- [pandas str.contains: Filter Rows by Text in Python](/blog/data-analysis/pandas-str-contains)
- [How to Count Cells With Text in Excel (Step by Step)](/blog/data-analysis/count-cells-with-text-excel)
- [Excel IS Functions: ISNUMBER, ISBLANK and More](/blog/data-analysis/excel-is-functions)