Excel IF Cell Contains Text: ISNUMBER(SEARCH), OR/AND, and COUNTIF Formulas

To return a result when a cell contains a word anywhere in it, use ISNUMBER(SEARCH()) as the IF test: =IF(ISNUMBER(SEARCH("apple",A2)),"Yes","No"). SEARCH returns the position of the text when it finds it and a #VALUE! error when it doesn't. ISNUMBER turns that into a clean TRUE or FALSE that IF, AND, and OR can work with.

How does ISNUMBER(SEARCH()) check if a cell contains text?

SEARCH looks for one piece of text inside another and returns the character position where it starts. If A2 holds "Green apple pie", =SEARCH("apple",A2) returns 7. If the text isn't there, SEARCH returns #VALUE!. Neither a position nor an error works as a TRUE/FALSE test, so ISNUMBER converts it: any position becomes TRUE, and the error becomes FALSE.

' TRUE if A2 contains "apple" anywhere, in any case
=ISNUMBER(SEARCH("apple", A2))

' Return your own labels
=IF(ISNUMBER(SEARCH("apple", A2)), "Fruit", "Other")

' Leave the cell blank when there is no match
=IF(ISNUMBER(SEARCH("apple", A2)), "Fruit", "")

' Keyword typed in E1 instead of inside the formula
=IF(AND($E$1<>"", ISNUMBER(SEARCH($E$1, A2))), "Match", "")

The $E$1<>"" guard in the last version matters. SEARCH treats an empty search term as found at position 1, so a blank E1 would mark every row as a match. SEARCH is one of the core Excel text functions, and if you need a refresher on IF itself, including nested IF, see the IF function examples.

How do I write: if cell = "Complete" return "Complete", if a cell contains the word "Not Started" return "Not Started"?

Use a nested IF that checks the exact match with = first and the partial match with ISNUMBER(SEARCH()) second. The = operator compares the whole cell and ignores case. SEARCH finds the phrase anywhere, so "Not started - waiting on vendor" still counts.

=IF(A2="Complete", "Complete",
 IF(ISNUMBER(SEARCH("not started", A2)), "Not Started", ""))

A2	Result	Why
Complete	Complete	Exact match
complete	Complete	= ignores case
Not started - waiting on vendor	Not Started	Contains "not started"
In progress	(blank)	Neither test is true
Incomplete	(blank)	Not an exact match for "Complete"

In Excel 2019 and later, IFS reads more cleanly once the chain gets longer:

=IFS(A2="Complete", "Complete",
     ISNUMBER(SEARCH("not started", A2)), "Not Started",
     TRUE, "")

Order matters whenever one keyword sits inside another. If you changed the first test to a contains check, "complete" would also match "Incomplete", and a test for "started" would match "Not started". Put the longer, more specific phrase first, or keep the exact = test for any status that has to match the whole cell.

How do I check if a cell contains one of several words (OR) or all of them (AND)?

Put one ISNUMBER(SEARCH()) test per keyword inside OR when any keyword should count, or inside AND when every keyword has to appear. You can also pass the keywords as an array constant in curly braces, which keeps the formula short.

' Any of the words (OR)
=IF(OR(ISNUMBER(SEARCH("red", A2)), ISNUMBER(SEARCH("blue", A2))), "Match", "")

' Same idea with an array constant
=IF(OR(ISNUMBER(SEARCH({"red","blue","green"}, A2))), "Match", "")

' All of the words (AND)
=IF(AND(ISNUMBER(SEARCH({"red","large"}, A2))), "Match", "")

' Keywords listed in H2:H6, Excel 365/2021
=IF(OR(ISNUMBER(SEARCH($H$2:$H$6, A2))), "Match", "")

' Keywords listed in H2:H6, any version
=IF(SUMPRODUCT(--ISNUMBER(SEARCH($H$2:$H$6, A2)))>0, "Match", "")

In Excel 2019 and earlier, the OR version that points at a range needs Ctrl+Shift+Enter; the SUMPRODUCT version doesn't. When the keywords live in cells, a blank cell in the list matches every row, which is covered in checking if a value exists in a list.

SEARCH also accepts wildcards, which helps when the wording varies. SEARCH("no*insulation", A2) matches "No insulation", "no-insulation", and "no top insulation". It will also match any text with "no" somewhere before "insulation", such as "Nominal insulation thickness", so spot-check what it flags.

How do I use =SEARCH("orange",$B2&$C2&$D2) to check several cells at once?

Joining the cells with & gives SEARCH one long string to scan, so the test is TRUE if "orange" appears in any of B2, C2, or D2. Wrap it in ISNUMBER, and return "" as the "no" value so non-matching rows stay blank instead of showing FALSE or #VALUE!.

' "Orange" if any of B2, C2, D2 contains it, otherwise blank
=IF(ISNUMBER(SEARCH("orange", $B2&"|"&$C2&"|"&$D2)), "Orange", "")

' Alternative for adjacent cells: count the cells that contain it
=IF(COUNTIF($B2:$D2, "*orange*")>0, "Orange", "")

The "|" separators are there on purpose. Without them, B2 = "Color" and C2 = "Angel" join into "ColorAngel", which contains "orAnge", so SEARCH reports "orange" even though neither cell holds it. Any character that never appears in your data works as a separator. The dollar signs lock the columns so the references don't shift if you copy the formula sideways, while the row still adjusts as you fill down. If the search term comes from a cell rather than the formula, add the same not-blank guard shown earlier.

Why does =AND(C2>80,SEARCH("poor",D2)) give the correct result for some rows but #VALUE! for others?

It only works when "poor" is found. SEARCH then returns a position such as 1 or 12, AND treats any nonzero number as TRUE, and the result looks right. When D2 doesn't contain "poor", SEARCH returns #VALUE!, and AND passes that error through instead of returning FALSE. The fix is to wrap SEARCH in ISNUMBER:

' Breaks when D2 doesn't contain "poor"
=AND(C2>80, SEARCH("poor", D2))

' Always returns TRUE or FALSE
=AND(C2>80, ISNUMBER(SEARCH("poor", D2)))

' As a complete IF
=IF(AND(C2>80, ISNUMBER(SEARCH("poor", D2))), "Review", "")

The same problem shows up in a plain IF: =IF(SEARCH("poor",D2),"Yes","No") never returns "No". It returns #VALUE! whenever the word is missing.

How do I combine a contains test with number conditions, like a defect rate threshold?

Put each ISNUMBER(SEARCH()) test inside AND with the number comparison it depends on, then join the separate rules with OR. In this defect log, column A holds the defect description, B the defect rate, and C whether the part is sortable. A row is flagged "For TPS" if any one of three rules is true:

• The defect rate is 5% or more, whatever the defect.
• The defect mentions "no insulation", "incomplete print", or "agglomeration", and the rate is 2% or more.
• The defect mentions "model mixing", and either the rate is 0.2% or more or the part isn't sortable.

Rows with no defect rate yet should stay blank. The formula in D2:

=IF(B2="", "",
 IF(OR(B2>=5%,
       AND(OR(ISNUMBER(SEARCH({"no insulation","incomplete print","agglomeration"}, A2))), B2>=2%),
       AND(ISNUMBER(SEARCH("model mixing", A2)), OR(B2>=0.2%, C2="No"))),
    "For TPS", ""))

A: Defect	B: Defect rate	C: Sortable?	D: Result	Rule that decides it
No insulation on lead	3%	Yes	For TPS	Keyword, and 3% is at least 2%
Agglomeration	1%	Yes	(blank)	Keyword, but 1% is under 2%
Model mixing	0.1%	No	For TPS	Model mixing, and not sortable
Model mixing	0.1%	Yes	(blank)	Under 0.2%, and sortable
Scratch	6%	Yes	For TPS	6% is at least 5%
Scratch	(blank)	Yes	(blank)	No rate yet

In an Excel Table, the same formula works with structured references: replace A2 with [@Defect], B2 with [@[Defect rate]], and C2 with [@[Sortable?]]. Line breaks inside a formula (Alt+Enter in the formula bar on Windows) don't change the result, and they make long AND/OR logic much easier to check against the rules.

Should I use SEARCH or FIND for a case-sensitive contains check?

Use FIND when case matters and SEARCH when it doesn't. They take the same arguments and both return #VALUE! when the text is missing, so ISNUMBER(FIND()) drops straight into any formula on this page. FIND is case-sensitive and treats * and ? as ordinary characters. SEARCH ignores case and treats them as wildcards.

' A2 contains "Order ID: PO-1042"
=ISNUMBER(SEARCH("po", A2))    → TRUE   (SEARCH ignores case)
=ISNUMBER(FIND("po", A2))      → FALSE  (no lowercase "po")
=ISNUMBER(FIND("PO", A2))      → TRUE

For an exact, case-sensitive match on the whole cell rather than part of it, use EXACT(A2,"Complete").

Can I use COUNTIF with wildcards instead of ISNUMBER(SEARCH())?

Yes. COUNTIF(A2,"*apple*") returns 1 if A2 contains "apple" and 0 if it doesn't, and IF treats any nonzero number as TRUE and 0 as FALSE. The asterisks mean "any characters before and after", and like SEARCH, COUNTIF ignores case. The COUNTIF guide covers the other wildcard patterns.

=IF(COUNTIF(A2, "*apple*"), "Yes", "No")

' Keyword in E1
=IF(COUNTIF(A2, "*"&$E$1&"*"), "Yes", "No")

' Any of several keywords
=IF(SUM(COUNTIF(A2, {"*no insulation*","*incomplete print*","*agglomeration*"}))>0, "Yes", "No")

COUNTIF has limits that SEARCH doesn't. Its first argument must be a range, so you can't pass it a joined string like B2&C2&D2 or the output of another function. Its wildcards only match text, so a cell holding the number 1500 won't match "*150*", while SEARCH converts the number to text and finds it. It also has no case-sensitive mode. Where COUNTIF wins is checking a whole range in one step, such as COUNTIF(B2:D2,"*orange*").

Which "contains" method should I use?

Default to ISNUMBER(SEARCH()). It works in every Excel version, handles numbers and joined cells, and plugs directly into IF, AND, and OR. Switch to FIND for case-sensitive checks, and to COUNTIF when you want to test a range of cells in one step.

Test	Matches	Case-sensitive	Wildcards	Best for
ISNUMBER(SEARCH("x",A2))	Anywhere in the cell	No	Yes	Default choice, joined cells, numbers
ISNUMBER(FIND("x",A2))	Anywhere in the cell	Yes	No	Codes and IDs where case matters
COUNTIF(A2,"*x*")>0	Anywhere, text cells only	No	Yes	Testing a range such as B2:D2
A2="x"	Whole cell only	No	No	Exact status values
EXACT(A2,"x")	Whole cell only	Yes	No	Exact, case-sensitive values

Common mistakes: why does my IF contains formula return the wrong result?

Most wrong results trace back to one of these six causes, so check them before rebuilding the formula.

• Using SEARCH without ISNUMBER. A bare SEARCH returns #VALUE! when the text is missing, which breaks IF, AND, and OR. Test ISNUMBER(SEARCH(...)) instead.
• Putting wildcards in an = comparison. =IF(A2="*apple*","Yes","No") looks for the literal text *apple*. The = operator doesn't support wildcards, so use SEARCH or COUNTIF.
• Short keywords matching inside longer words. "complete" is found inside "Incomplete", and "cat" inside "category". Test the longer phrase first, or pad both sides with spaces to match whole words: ISNUMBER(SEARCH(" cat ", " "&A2&" ")). The padding trick misses words next to punctuation, such as "cat,".
• A blank search term. When the keyword comes from a cell and that cell is empty, SEARCH returns 1 and every row matches. Add $E$1<>"" to the test.
• Searching for a literal * or ?. SEARCH reads them as wildcards, so SEARCH("?",A2) matches any non-empty cell. Put a tilde in front ("~?") or use FIND, which has no wildcards.
• Hidden spaces. An exact test like A2="Complete" fails on "Complete " with a trailing space. Use TRIM(A2)="Complete", or switch to a contains test if extra text is acceptable.