You stare at column A, scroll through 4,000 rows, and your boss wants a flag whenever a cell contains the word "urgent" anywhere inside the message. A plain =IF(A2="urgent","Yes","No") will not work because the cell holds a paragraph, not a single word. This is the moment every Excel user runs into the same wall, and the fix is one of the most useful patterns in the entire program: IF combined with a text-search function.

Excel has no built-in CONTAINS operator. That trips people up. Instead, you build the logic from two ingredients: a search function that returns a position number (SEARCH or FIND), and a wrapper that converts that number into a TRUE/FALSE you can feed into IF. The most common formula looks like this: =IF(ISNUMBER(SEARCH("urgent",A2)),"Yes","No").

Drop it in B2, drag it down, done. But there are pitfalls. SEARCH is case-insensitive while FIND is case-sensitive. Both return a #VALUE! error when the text is missing, which is why you need ISNUMBER or IFERROR. Wildcards behave differently inside each function. The next sections walk through every variation, edge case, and shortcut you will run into.

Why the Syntax Looks Weird

Before diving into formulas, it helps to understand why the syntax looks weird. Excel treats text searches as positions, not matches. When SEARCH finds the word "urgent" starting at character 5 of cell A2, it returns the number 5. If the word is missing, it returns an error, not zero, not FALSE. That is the design choice everything else flows from.

Wrapping the call in ISNUMBER converts "found" (a number) into TRUE and "not found" (an error) into FALSE. From there, an IF function can do whatever you want, including return another formula, a calculation, or a blank string.

The companion Excel IF contains text guide covers the same idea from a different angle, focused on partial-match shortcuts. Together they cover roughly 95 percent of real-world checking tasks people throw at spreadsheets. If you are brand new to the IF function itself, start with our Excel IF statement explainer first, because everything below assumes you already know how IF, AND, and OR plug together.

Method 1: ISNUMBER + SEARCH (the default approach)

This is the formula you should memorize. =IF(ISNUMBER(SEARCH("apple",A2)),"Contains","Missing") reads: search cell A2 for the word "apple"; if SEARCH returns a number (meaning it found it somewhere), output "Contains", otherwise output "Missing". SEARCH ignores capitalization, so "APPLE", "Apple", and "apple" all match. Wildcards work too: SEARCH("app*",A2) matches "app", "application", "apparent", anything starting with those three letters.

A subtle benefit of ISNUMBER is that it never throws an error. Compare that to writing =IF(SEARCH("apple",A2)>0,"Yes","No"), which crashes with #VALUE! on the rows that do not contain "apple" because IF receives an error instead of a number. ISNUMBER catches that gracefully.

You can also nest the formula inside other logic, for instance categorizing rows: =IF(ISNUMBER(SEARCH("refund",A2)),"Returns",IF(ISNUMBER(SEARCH("invoice",A2)),"Billing","Other")). That nested IF tags any row mentioning refunds, then anything mentioning invoices, then a fallback. The nested IF guide goes deep on those layered structures if you need more than two categories.

Method 2: IFERROR + FIND (when case matters)

FIND behaves exactly like SEARCH except it is case-sensitive and rejects wildcards. Use it when capitalization is meaningful, for example detecting all-caps "URGENT" but ignoring lowercase "urgent". The pattern looks like =IFERROR(IF(FIND("URGENT",A2)>0,"Flag",""),""). IFERROR replaces the #VALUE! error with a blank string instead of converting it to FALSE the way ISNUMBER does.

The choice between IFERROR+FIND and ISNUMBER+SEARCH usually comes down to whether you care about case. One gotcha: FIND throws an error on empty strings too, which can cascade through long workbooks. Wrap it in IFERROR even when you think the inputs are clean. Real-world data always surprises you. Pair this with the Excel SEARCH function deep-dive to see the side-by-side comparison.

Method 3: COUNTIF with Wildcards (count instead of flag)

Sometimes you do not want a TRUE/FALSE flag. You want a tally: how many cells in column A contain the word "error"? =COUNTIF(A2:A100,"*error*") answers that directly. The asterisks tell COUNTIF to accept any characters before or after the keyword. This is the simplest possible "contains" check and it works inside a single function call, no IF required. It is also case-insensitive, which matches SEARCH behavior.

If you need a row-by-row flag rather than a global count, use COUNTIF inside an IF: =IF(COUNTIF(A2,"*error*")>0,"Has Error","Clean"). That is a slightly slower pattern than ISNUMBER+SEARCH because COUNTIF is a heavier function, but it is easier to read for non-technical teammates. The trade-off is yours. Our Excel COUNTIF reference covers wildcard rules in detail.

Method 4: ISTEXT (when you only care that anything is text)

Be careful not to confuse "contains specific text" with "is text at all". The ISTEXT function answers the second question: =IF(ISTEXT(A2),"Text","Not text") returns TRUE when A2 holds any string, including a single space or an empty string formula. It says nothing about which text. People mix this up when they want to validate input forms. If you need "is this a non-numeric value" use ISTEXT. If you need "does this string contain the word balance" use ISNUMBER+SEARCH.

VLOOKUP Versus Contains Check: Pick the Right Tool

Beginners sometimes reach for VLOOKUP when they really need a contains check, and vice versa. The two tools answer different questions. VLOOKUP says "give me the matching record from another table". Contains says "does this cell mention this keyword anywhere inside it". If your task is to look up a customer ID and pull their email from a master list, VLOOKUP wins. If your task is to scan free-text descriptions for keywords, contains wins.

The Excel VLOOKUP guide covers the lookup scenario in depth. Mix them up and you waste hours debugging the wrong formula. One genuinely useful hybrid: VLOOKUP with wildcards. =VLOOKUP("*"&A2&"*",List,2,FALSE) finds a partial match inside another table. It is the closest thing Excel has to a "fuzzy lookup" without buying an add-in.

Handling Multiple Keywords at Once

The boss now says: flag the row if it contains "refund" OR "cancel" OR "complaint". You have two clean approaches. The OR-style chain looks like =IF(OR(ISNUMBER(SEARCH("refund",A2)),ISNUMBER(SEARCH("cancel",A2)),ISNUMBER(SEARCH("complaint",A2))),"Action Needed",""). It works, but it gets ugly past three or four keywords.

The cleaner version uses SUMPRODUCT and a list of keywords on another sheet: =IF(SUMPRODUCT(--ISNUMBER(SEARCH(KeywordList,A2)))>0,"Action Needed",""), where KeywordList is a named range on a config sheet. Now you can edit the list without touching the formula. This is the pattern you want in any workbook that gets handed off to teammates.

For AND logic (the row must contain ALL keywords), nest AND instead of OR: =IF(AND(ISNUMBER(SEARCH("urgent",A2)),ISNUMBER(SEARCH("invoice",A2))),"Yes",""). Or use the SUMPRODUCT trick with COUNTA. The match count must equal the keyword count.

Wildcards: When * and ? Actually Help

SEARCH accepts two wildcards. The asterisk matches zero or more characters; the question mark matches exactly one. SEARCH("c?t",A2) matches "cat", "cot", "cut" but not "coat" or "ct". SEARCH("c*t",A2) matches all of those plus "coat", "client", "comment". COUNTIF and SUMIF accept the same wildcards. FIND does not. If your keyword itself contains a literal asterisk or question mark, escape it with a tilde: SEARCH("100~%",A2) looks for the literal substring "100%".

Returning a Calculation Instead of "Yes" or "No"

IF does not have to return text. It can return a number, another formula, or a cell reference. For example, "if A2 contains the word discount, apply a 10 percent reduction to B2, otherwise keep B2 as is": =IF(ISNUMBER(SEARCH("discount",A2)),B2*0.9,B2). Or "if the description contains shipping, add 5 to the freight column": =IF(ISNUMBER(SEARCH("shipping",A2)),C2+5,C2). This is where the contains-pattern gets genuinely powerful.

You can nest the pattern inside SUMIFS, AVERAGEIFS, or array formulas too. =SUMPRODUCT((ISNUMBER(SEARCH("Q4",A2:A100)))*(B2:B100)) sums column B only for rows whose A column mentions Q4. That single formula replaces an entire pivot table for many one-off reports. Pair it with the Excel SUMIF guide for the version using direct text comparison, and Excel SUMIFS for multi-criteria sums.

Performance Notes for Large Datasets

SEARCH and FIND are fast on individual cells but slow when you drag them down 100,000 rows. If you notice the workbook hanging, three speed tweaks help. First, lock the keyword in an absolute reference and refer to it instead of hardcoding the string: =IF(ISNUMBER(SEARCH($D$1,A2)),"Yes","No"). Excel can cache that lookup once instead of re-parsing the literal string on every row.

Second, switch to manual calculation mode while you are dragging formulas down: press F9 to recalculate when ready. Third, if the worksheet is truly massive, consider Power Query instead. Power Query has a "Text.Contains" step that handles 5 million rows without breaking a sweat, and once you set the transformation it refreshes on click.

Real-World Example: Customer Feedback Tagging

Say you exported 8,000 support tickets and the ticket subject is in column A. You want columns B through F flagged with TRUE whenever the subject contains one of five categories: billing, login, bug, feature, refund. Drop these formulas in row 2 and drag down:

• B2: =ISNUMBER(SEARCH("billing",A2))
• C2: =ISNUMBER(SEARCH("login",A2))
• D2: =ISNUMBER(SEARCH("bug",A2))
• E2: =ISNUMBER(SEARCH("feature",A2))
• F2: =ISNUMBER(SEARCH("refund",A2))

Now run a SUM on each column to see category totals. Or use the boolean flags inside a pivot table for cross-tab analysis. This is the actual workflow that gets used in real analyst jobs.

Edge Case: Numbers Stored as Text

If column A holds numbers that arrived from a database export, they may actually be text strings, not numbers. SEARCH will find them just fine because SEARCH treats everything as text. But you might be tempted to mix in a numeric comparison. Do not. Pick one approach: either convert the column with Excel convert text to number first, or stick to text comparisons throughout. Mixing types causes IF to return inconsistent results that are very hard to debug.

A related quirk: leading zeros disappear when Excel auto-converts strings to numbers. If you import order codes like "00451" and Excel turns them into 451, your contains-check for "00" suddenly fails on every row. Format the column as Text before pasting, or prefix with an apostrophe to force text mode. This single habit prevents about a third of the contains-formula debugging questions that show up on help forums.

REGEX in Newer Excel Builds

Microsoft rolled out REGEXTEST, REGEXEXTRACT, and REGEXREPLACE to current channel Microsoft 365 in 2024. For users on those builds the formula simplifies dramatically: =IF(REGEXTEST(A2,"apple"),"Yes","No"). REGEXTEST handles patterns ISNUMBER+SEARCH cannot, like "matches any word starting with a capital letter" or "matches a phone number pattern". For most "does cell contain word" tasks the ISNUMBER+SEARCH pattern remains faster and works on every version of Excel back to 2007, so it is still the safe default.

If you do have REGEX available, a powerful trick is matching whole words only. The classic ISNUMBER+SEARCH approach flags "cat" inside "category" or "concatenate", which can be a problem. With REGEXTEST you write =REGEXTEST(A2,"\bcat\b") where the word-boundary anchors prevent partial-word matches.

Common Errors and How to Fix Them

The three errors you will hit most often: #VALUE! when SEARCH or FIND cannot locate the text and you forgot ISNUMBER; #NAME? when you misspelled the function or are on an old build that lacks REGEXTEST; and the formula returning the wrong branch because you mixed up the order of IF arguments. IF takes condition first, then value-if-true, then value-if-false. If your "Yes" and "No" are swapped, you have the arguments reversed.

There is also a quieter bug: when the search keyword is an empty string, SEARCH returns 1 (technically a match at position 1) and your formula will flag every row. Defend against this by wrapping in IF(D1="","",...) when the keyword sits in a cell that a user can clear.

Another sneaky issue: trailing spaces. A cell that looks like "apple" might actually contain "apple " with a space at the end, often coming from copy-paste or CSV imports. The contains-formula still works because SEARCH ignores trailing characters after the match, but exact-match formulas like =A2="apple" will fail. If your contains-formula behaves strangely on data imported from another system, run TRIM on the source column first. Extract text in Excel covers TRIM, CLEAN, and the other text-prep utilities you typically need before searching.

Combining IF Contains With Conditional Formatting

The same pattern feeds Conditional Formatting rules. Select your range, choose New Rule, Use a formula, and type =ISNUMBER(SEARCH("urgent",A1)). Note the lack of IF here. Conditional Formatting takes a formula that evaluates to TRUE or FALSE directly, so you skip the IF wrapper. Pick your fill color and click OK. Every cell in the range containing "urgent" now lights up.

Combine this with a quick find duplicates in Excel pass to clean duplicate flagged rows before delivering the report. And if column A holds numbers that arrived from a database export, they may actually be text strings, not numbers. SEARCH will find them just fine because SEARCH treats everything as text. But you might be tempted to mix in a numeric comparison. Do not. Pick one approach: either convert the column with Excel convert text to number first, or stick to text comparisons throughout.

Ready to Practice?

The fastest way to lock this in is to drill realistic scenarios. Build a 50-row table of fake support tickets, write the IF+ISNUMBER+SEARCH pattern for three different keywords, then stress-test it with edge cases. After that, the pattern becomes muscle memory and you will type it without thinking. Walking through real practice questions is the fastest path from "I can copy this formula" to "I can write this from scratch in any situation".