Stop Searching for CONTAINS — Excel Has 4 Real Ways to Do It

By Sarah Mitchell
The first thing most people do when they need to check if a cell contains specific text is type =CONTAINS(A1,"apple") and hit Enter. You get #NAME? — and then you Google 'why does excel have a contains function' or 'how to make contains work in excel'. That’s the wrong starting point entirely. You’re not missing a feature. You’re looking for something that never existed — and wasting time while overlooking four perfectly functional tools already installed on your machine.

The Myth

Most people believe Excel has (or should have) a native CONTAINS function — like SQL’s CONTAINS() or Power BI’s CONTAINSSTRING(). They assume it’s just hidden, misnamed, or buried under an obscure ribbon tab. Some even download add-ins or install custom VBA functions hoping to ‘unlock’ it. That’s not how Excel works. There is no CONTAINS — not in Excel 365, not in Excel 2019, not in any version released since 1995. The error isn’t yours. The expectation is.

The Reality

What Excel *does* have are four distinct, well-documented, and fully supported formulas that handle ‘contains’ logic — each with different strengths. None are named CONTAINS, but all deliver precise substring detection. Here’s how they compare across real-world use cases:
Function Syntax Example Case-Sensitive? Returns TRUE/FALSE? Best For
ISNUMBER + SEARCH =ISNUMBER(SEARCH("CRM",A2)) No Yes Quick filtering, conditional formatting
FIND =ISNUMBER(FIND("CRM",A2)) Yes Yes Exact-match workflows (e.g., password checks)
COUNTIF with wildcards =COUNTIF(A2,"*CRM*")>0 No Yes Arrays & spill ranges (Excel 365 only)
XLOOKUP + ISNUMBER + SEARCH =NOT(ISERROR(XLOOKUP("*CRM*",A2:A10,A2:A10,,2))) No Yes Searching across entire columns (no array entry needed)

Why the Myth Persists

You’ll find dozens of YouTube videos titled "How to Add CONTAINS Function to Excel" — many uploaded between 2016–2020. Those creators were responding to real pain, but misdiagnosed the cause. Back then, Excel lacked dynamic arrays and spilled formulas. People tried SUMPRODUCT(--ISNUMBER(SEARCH(...))) and gave up after three nested parentheses. So tutorials started calling it "the CONTAINS workaround" — and the name stuck. Even Microsoft’s own support pages once used "contains" informally in examples (like "check if cell contains text"). That language bled into forum posts, Stack Overflow answers, and eventually became folklore.

The Right Way

Let’s walk through the simplest, most widely compatible method: ISNUMBER(SEARCH()). Say you’re auditing vendor notes in column A (A2:A11) and want to flag any row mentioning "Acme Corp" or "delay". Start in B2 with: =ISNUMBER(SEARCH("Acme Corp",A2)) Then copy down to B11. That’s it. No add-ins. No macros. No Ctrl+Shift+Enter. Now try this variation — it handles multiple terms at once, without nesting: =OR(ISNUMBER(SEARCH("Acme Corp",A2)),ISNUMBER(SEARCH("delay",A2))) That goes in C2. You’ll see TRUE if *either* phrase appears. Here’s the sample data you’d be working with:
A2:A11 (Vendor Notes) B2:B11 (Contains "Acme Corp"?) C2:C11 (Contains "Acme Corp" OR "delay"?)
Invoice #7821 — Acme Corp — shipped 2024-03-15 TRUE TRUE
Payment received from BetaLabs Inc. FALSE FALSE
Shipment delayed — awaiting Acme Corp approval TRUE TRUE
Refund processed for Gamma Systems Ltd. FALSE FALSE
Urgent: delay in fulfillment — contact Acme Corp TRUE TRUE
PO#9922 — Zenith Dynamics — paid FALSE FALSE
Acme Corp follow-up required by EOD TRUE TRUE
Credit memo issued — Delta Solutions FALSE FALSE
One counterintuitive tip: SEARCH ignores case, but it *does* respect leading/trailing spaces. If your source data has extra spaces (e.g., " Acme Corp "), wrap the search term in TRIM(): =ISNUMBER(SEARCH(TRIM("Acme Corp"),A2)). I learned this debugging a report where "Apple" matched "applesauce" — because someone had typed "Apple " with a trailing space in the lookup table.

Proof It Works

Here’s what your raw data looks like before applying the formula — and how it transforms with one simple expression:
Before (A2:A6) After (B2:B6 with =ISNUMBER(SEARCH("delay",A2)))
Delay confirmed — reship on 2024-04-02 TRUE
Shipped via DHL Express FALSE
Client requested delay — approved 2024-03-28 TRUE
Pending QA review FALSE
Delay waived per contract clause 4.2 TRUE

Exceptions

There *is* one scenario where typing =CONTAINS(...) won’t return #NAME? — and that’s if you’re using Power Query (Get & Transform). In Power Query’s M language, Text.Contains() is real, documented, and case-insensitive by default. So if your workflow starts in Power Query — not the worksheet — then yes, you *do* have a CONTAINS-like function. Just don’t expect it to work in a cell. Also: Excel for the web supports ISNUMBER(SEARCH()) identically to desktop, but some legacy versions (Excel 2007 and earlier) lack dynamic array support — meaning COUNTIF with wildcards won’t spill. Stick with ISNUMBER(SEARCH()) there. Ready to apply this? Try these three actions right now:
  • Press Alt + H + L to open Conditional Formatting → Highlight Cells Rules → Text that Contains… — no formula needed for basic highlighting
  • Type =ISNUMBER(SEARCH("your term",A2)) in an empty column beside your data — then double-click the fill handle to copy down
  • Select your data range (e.g., A2:C11), press Ctrl + T to convert to a Table — formulas auto-fill and update dynamically
Sarah Mitchell

Sarah Mitchell

Sarah has 12 years of experience covering Microsoft 365 productivity tools and enterprise software workflows. She specializes in Excel automation and SharePoint integration.