IF Cell Contains Text
Excel and Google Sheets have no "contains" operator, so IF needs a helper test: =IF(ISNUMBER(SEARCH("urgent",A2)),"Flag","") returns "Flag" whenever A2 contains "urgent" anywhere in its text, case-insensitive. SEARCH finds the position of the substring or returns an error when it is missing, and wrapping it in ISNUMBER turns that into a clean TRUE or FALSE for IF to test. To count how many cells in a range contain the text, rather than test one cell, COUNTIF with wildcards does the job directly: =COUNTIF(A2:A20,"*urgent*") needs no ISNUMBER or SEARCH, since COUNTIF's wildcard matching already behaves like a "contains" test.
The formula
=IF(ISNUMBER(SEARCH("urgent",A2)),"Flag","")IF + SEARCH: TRUE test for "contains", case-insensitive=IF(ISNUMBER(SEARCH("urgent",A2)),A2,"")same test, returns the cell's own text instead of a label=IF(ISNUMBER(FIND("urgent",A2)),"Flag","")case-SENSITIVE version: FIND instead of SEARCH=COUNTIF(A2:A20,"*urgent*")count cells containing the text anywhere, not an exact matchA worked example
A2 holds the note "Ship urgent - customer waiting" and the goal is a formula that flags any row mentioning "urgent".
=IF(ISNUMBER(SEARCH("urgent",A2)),"Flag","")
"Flag" — SEARCH is case-insensitive, so it matches "urgent", "Urgent" or "URGENT" alike. A plain =IF(A2="urgent","Flag","") would return "" here instead, since A2 holds a whole sentence, not just the single word "urgent".
Which one do I need?
| If you want to… | Use |
|---|---|
| Testing one cell for text anywhere inside it | =IF(ISNUMBER(SEARCH("text",A2)),"Flag","") |
| The match needs to be case-sensitive ("Urgent" but not "urgent") | Swap SEARCH for FIND: =IF(ISNUMBER(FIND("text",A2)),"Flag","") |
| Returning the matched cell's own value instead of a fixed label | =IF(ISNUMBER(SEARCH("text",A2)),A2,"") |
| Counting how many cells in a range contain the text, rather than testing a single cell | =COUNTIF(A2:A20,"*text*") |
| The text to search for is itself stored in another cell, not typed into the formula | Build the wildcard with &: =COUNTIF(A2:A20,"*"&B1&"*") matches whatever B1 currently holds |
Frequently asked questions
Why does =IF(A2="urgent","Flag","") return blank even though A2 clearly mentions "urgent"?
The = operator tests for an exact match against the whole cell, not whether the text appears somewhere inside it. A2 holding "Ship urgent - customer waiting" is not equal to "urgent", so the test fails. Use =IF(ISNUMBER(SEARCH("urgent",A2)),"Flag","") for a genuine "contains" test.
What's the difference between SEARCH and FIND for this kind of test?
SEARCH is case-insensitive and matches "urgent", "Urgent" or "URGENT" alike; FIND is case-sensitive and only matches the exact casing given. Both return the position of the match or an error if the text is missing, which is why both get wrapped in ISNUMBER before feeding IF.
Does this work the same in Google Sheets?
Yes — SEARCH, FIND, ISNUMBER and COUNTIF with wildcards all behave identically in Google Sheets, with the same case-sensitivity difference between SEARCH and FIND.
Why did COUNTIF miss a cell that clearly contains the text I searched for?
COUNTIF treats * and ? inside the criteria as wildcards. If the text you are matching against contains a literal asterisk or question mark, COUNTIF reads it as a wildcard instead of a character to match, so the count comes out wrong. Prefix it with a tilde to match it literally: "*~**" matches any cell containing an actual * character, rather than treating that * as "any text".
New guides and tools, once a month
DE + EN · double opt-in · no spam