Wildcard Filters in Excel: Every Method Explained

A colleague of mine, sharp, fast, and reliable, spent every Friday afternoon scrolling through 4,000 rows of product data, manually hunting for SKUs that contained a specific supplier code buried somewhere in the middle. Three hours, every week. I watched her do it for a month before I leaned over and typed two characters into a filter box. She stared at the screen. "That's it?" That's it. Wildcard filters excel at exactly this problem: you know part of a value, not all of it, and you need Excel to find everything that matches.

The catch is that there are two very different ways to apply wildcard filters in Excel — AutoFilter and the FILTER function — and they don't work the same way. Pick the wrong one and you'll either get no results or a #CALC! error that makes you feel like you've done something wrong. You haven't. The tool just expects a different approach. Here's how to pick the right one.

The Three Wildcard Characters You Need to Know

Excel recognizes three wildcard characters. The asterisk (*) matches any number of characters — zero, one, or fifty. So *Smith* catches "Smith," "Blacksmith," and "Smith & Associates." The question mark (?) matches exactly one character, useful when you know a value's length but not one specific position, like P???-04 to catch part codes of a fixed format. The tilde (~) is the escape character — it's the one most tutorials skip, and it matters more than you'd think.

AutoFilter vs. FILTER Function: Which One to Use

If you're still getting familiar with filtering in Excel generally, the introduction to sorting and filtering in Excel covers the foundational concepts before we get into the wildcard-specific behavior here.

---

Step 1: Use Wildcard Filters in AutoFilter for Instant Partial Matches

Once you've decided AutoFilter fits your situation, the mechanics are simple — but there's one behavior that surprises people every time.

Filtering Text and Numbers with AutoFilter Wildcards

To apply a wildcard filter in AutoFilter:

1. Click any cell in your data range and go to Data → Filter to enable the dropdown arrows.
2. Click the dropdown arrow on the column you want to filter.
3. Select Text Filters → Contains (or Custom Filter if you want full control).
4. In the Custom AutoFilter dialog, type your wildcard pattern — for example, *-WEST-* to catch any value with "-WEST-" anywhere inside it.
5. Click OK.

One thing that catches people off guard: wildcards in AutoFilter work on numbers too. If your column contains numeric invoice IDs stored as text — or even as actual numbers — 2024* will surface everything that starts with 2024. That's not obvious from the "Text Filters" label, but it works consistently even on datasets well over 50,000 rows.

Wildcards in AutoFilter apply to both text and number columns. You don't need to convert numeric data to text for this to work.

Here's the part most tutorials skip entirely. If your data contains a literal asterisk or question mark — think product codes like SIZE*L or part numbers formatted with a ? — you can't just type those characters into the filter box. Excel will treat them as wildcards and return garbage results. The fix is the tilde: type ~* to match a literal asterisk, or ~? to match a literal question mark.

If your data includes literal asterisks or question marks in cell values, always prefix them with a tilde (~) in your filter pattern. Skipping this step will cause unpredictably broad results.

One more habit worth building before you filter: run TRIM on your data first, or at least be aware of trailing spaces. A wildcard filter for *Smith* will miss Smith (trailing space) in certain filter contexts. It's a two-second check that prevents an hour of head-scratching. The same logic that applies to VLOOKUP debugging applies here. Excel isn't being picky about your data — it's being literal. There's a difference.

---

Step 2: Use ISNUMBER and SEARCH for Wildcard Filters Inside the FILTER Function

AutoFilter works well for one-off filtering. But if you're building a report where the output needs to refresh automatically as your source data changes, you want the FILTER function — and that's where things get a little more technical.

Why Wildcards Break Directly Inside FILTER

This trips up a lot of people. If you type something like =FILTER(A2:B100, B2:B100="*West*"), Excel returns a #CALC! error. The FILTER function doesn't process wildcard characters the same way AutoFilter does — it reads *West* as a literal string, finds no exact matches, and throws an error instead of returning an empty result. That's not a bug. It's just how the function was designed.

The fix is to replace the wildcard logic with ISNUMBER(SEARCH()):

=FILTER(A2:B100, ISNUMBER(SEARCH("West", B2:B100)))

SEARCH scans each cell in the range for the substring "West" and returns its position if found, or an error if not. ISNUMBER converts those results to TRUE/FALSE, which is exactly what FILTER needs. No asterisks required — the SEARCH function handles the "contains" logic on its own. This is the standard workaround for using wildcard-style partial matching inside FILTER, though it's rarely explained this clearly.

Making It Case-Sensitive with FIND Instead of SEARCH

SEARCH is case-insensitive — "west," "West," and "WEST" all match. That's usually what you want. But if you need case-sensitive wildcard filtering (say, filtering for product codes where capitalization distinguishes one category from another), swap SEARCH for FIND:

=FILTER(A2:B100, ISNUMBER(FIND("WEST", B2:B100)))

FIND behaves identically to SEARCH except it respects case. "WEST" won't match "West." Same formula structure, one function swap.

---

Common Wildcard Filter Mistakes in Excel

After years of debugging other people's spreadsheets, the same four mistakes come up repeatedly with wildcard filters.

Typing a wildcard directly into a FILTER formula. As covered above, this produces a #CALC! error every time. FILTER doesn't process * as a wildcard. Switch to ISNUMBER(SEARCH()) instead.

Forgetting the tilde before a literal asterisk. If your data includes values like 10*Pack and you filter for *10*Pack*, Excel reads both asterisks as wildcards and your results will be unpredictably broad. Use *10~*Pack* to tell Excel that the middle asterisk is a literal character.

Mixing up SEARCH and FIND. SEARCH is case-insensitive. FIND is case-sensitive. Grab the wrong one and you'll either get too many results or none at all, depending on your data.

Expecting COUNTIF wildcard behavior to match the FILTER function. COUNTIF accepts wildcards natively — =COUNTIF(B2:B100,"*West*") works fine. AutoFilter also accepts them natively. The FILTER function does not. These are three different tools with three different rules, and that inconsistency is genuinely frustrating. Knowing which is which saves real time.

Quick reference: * works natively in AutoFilter and COUNTIF, but not in the FILTER function. For FILTER, always use ISNUMBER(SEARCH()).

For more on how filtering fits into your broader data workflow, the guide to sorting and filtering data in Excel on Mac covers platform-specific behavior that affects some of these methods.

---