Excel IF Cell Contains Text: Formula Examples and Wildcards

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

To write an Excel IF cell contains formula, combine IF with SEARCH and ISNUMBER: =IF(ISNUMBER(SEARCH("text",A2)),"Yes","No"). SEARCH returns the position of the text inside the cell, ISNUMBER converts that into TRUE or FALSE, and IF turns it into the result you want. You can also use COUNTIF with wildcards, which gives the same result for a single cell.

Quick Answer

• The standard pattern is =IF(ISNUMBER(SEARCH("text",A2)),"Yes","No") [1].
• SEARCH is case-insensitive, so "Refund", "refund" and "REFUND" all match.
• COUNTIF with asterisk wildcards works too: =IF(COUNTIF(A2,"refund")>0,"Refund","Other").
• SEARCH finds the text anywhere in the cell, including the middle of a word.
• Use FIND instead of SEARCH when you need a case-sensitive test.

Syntax

The main formula uses three functions nested together. Here are the arguments for each.

The full formula in display form:

$$=IF(ISNUMBER(SEARCH("text",A2)),"Yes","No")$$

How It Works

SEARCH looks for find_text inside within_text and returns the character position where it first appears. If the text is not there, SEARCH returns the #VALUE! error instead of a number.

ISNUMBER then checks what came back. A position number is a number, so ISNUMBER returns TRUE. An error is not a number, so ISNUMBER returns FALSE. This is the trick that turns a search into a clean logical test [1].

IF takes that TRUE or FALSE and returns whichever result you supplied. That is the whole mechanism. The nesting order matters: IF wraps ISNUMBER, and ISNUMBER wraps SEARCH.

The COUNTIF version works differently. COUNTIF counts how many cells in a range match a criteria pattern. When the range is a single cell and the criteria is "refund", it returns 1 if the cell contains that text and 0 if it does not. Comparing the result to 0 with >0 gives you the TRUE or FALSE that IF needs.

The asterisk is a wildcard that stands for any number of characters, so "refund" means "anything, then refund, then anything". This is the same wildcard logic used in a COUNTIF cell contains text formula across a whole column.

Worked Example

This sheet holds customer support notes in column A. Column B tests whether each note mentions a refund. Columns C and D categorise each note as Refund or Other, using SEARCH and COUNTIF respectively.

Look at rows 3, 5, 8 and 10. Both columns return Refund even though the note starts with "Refund", because the asterisk matches zero or more characters and COUNTIF ignores case. For a single cell the two approaches agree, so choose whichever you find easier to read.

More Examples

Return a label instead of Yes or No. Swap the two result arguments for anything you like.

=IF(ISNUMBER(SEARCH("urgent",A2)),"Escalate","Normal queue")

Case-sensitive matching with FIND. FIND works like SEARCH but respects capitalization. Use it when "Refund" and "refund" should be treated differently, as covered in the Excel FIND function guide.

=IF(ISNUMBER(FIND("Refund",A2)),"Match","No match")

Test for any text at all. To check whether a cell holds text rather than a number, use ISTEXT.

=IF(ISTEXT(A2),"Text","Not text")

Combine with a second condition. Wrap the test in AND when you need two things to be true, using the pattern from IF AND statements in Excel.

=IF(AND(ISNUMBER(SEARCH("refund",A2)),B2>100),"High value refund","Review")

Return a blank instead of a label. Pass an empty string as the false result.

=IF(ISNUMBER(SEARCH("refund",A2)),"Refund","")

Count matches across a column. If you only need a total, skip IF and use COUNTIF directly. The Excel CONTAINS check article covers the counting side in more depth.

Errors and How to Fix Them

The #VALUE! error is the most common one. It is not a bug. It is SEARCH telling you the text was not found, which is exactly why ISNUMBER is part of the pattern.

Common Mistakes

• Forgetting ISNUMBER. Writing =IF(SEARCH("refund",A2),"Yes","No") looks reasonable but fails. A match returns Yes because any nonzero position counts as TRUE, but a non-match returns #VALUE! instead of No. Always wrap SEARCH in ISNUMBER.
• Dropping an asterisk from the COUNTIF pattern. "refund*" only matches cells that start with the word and "*refund" only matches cells that end with it. Keep both asterisks, "refund", to match the word anywhere, including at the very start, because an asterisk can stand for zero characters.
• Expecting SEARCH to be case-sensitive. It is not. If case matters, use FIND instead.
• Searching for a word without word boundaries. Searching for "refund" also matches "refunds" and "refunded". If that is wrong for your data, search for "refund " with a trailing space or use a more specific phrase.
• Leaving wildcard characters in the search term. If your text genuinely contains or ?, COUNTIF will treat them as wildcards. Escape them with a tilde, as in "~**".
• Hardcoding the search term in many formulas. Put the term in its own cell and reference it, so you can change it in one place.

Limitations

Neither approach understands meaning. A note saying "the customer did not want a refund" contains the word "refund" and will be flagged as a refund case. Keyword matching cannot detect negation, sarcasm or context, so treat the output as a first pass that a human reviews.

SEARCH also cannot tell you how many times the text appears, only whether it appears at least once. For counting occurrences you need a different technique, such as subtracting the length of the cell from the length after removing the search term. And if your data lives in a case where the search term is a substring of a common unrelated word, you will get false positives that no amount of formula tweaking will fully remove.

Frequently Asked Questions

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

Use =IF(ISNUMBER(SEARCH("text",A2)),"Yes","No"). SEARCH returns the position of the text or an error, ISNUMBER converts that to TRUE or FALSE, and IF returns your chosen result [1]. This works in Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019 and Excel 2016.

Is the IF cell contains formula case-sensitive?

No. SEARCH ignores capitalization, so "Refund", "refund" and "REFUND" all match. If you need a case-sensitive test, replace SEARCH with FIND. FIND uses the same arguments but treats uppercase and lowercase as different characters.

Can I use wildcards with IF and SEARCH?

Yes. SEARCH accepts the wildcards ? and * in find_text (use ~ to search for a literal one), although you rarely need them because SEARCH already finds text anywhere in the cell. FIND does not support wildcards. COUNTIF also works: =IF(COUNTIF(A2,"text")>0,"Yes","No"), and the asterisks also match text at the very start of the cell.

Why does my formula return #VALUE!?

SEARCH returns #VALUE! when it cannot find the search text. That is expected behavior, not a broken formula. Wrapping SEARCH in ISNUMBER catches the error and turns it into FALSE, which IF then handles normally. If you see #VALUE! in your final result, you probably left out ISNUMBER.

How do I return a different value when the text is not found?

Change the third argument of IF. =IF(ISNUMBER(SEARCH("refund",A2)),"Refund","Other") returns "Other" when there is no match. You can return a blank with "", a number, a cell reference or another formula. For multi-way categorisation, the IFS function keeps the logic readable when you have more than two outcomes.

References

1. Check if a cell contains text (case-insensitive) in Excel | Microsoft Support

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

Related Articles

• Excel CONTAINS: How to Check If a Cell Contains Text
• COUNTIF Cell Contains Text in Excel: Formula and Examples
• IFS Function in Excel: Syntax and Examples
• IF AND Statements in Excel: Syntax and Examples
• Excel TEXT Function: Syntax, Format Codes and Examples