THE EXCEL GUIDE Mastering Data & Spreadsheets

Home
Data Analysis and Sorting

Text Filters in Excel: Every Method, Every Fix

Learn how to filter data based on text conditions.
By:

Most Excel tutorials will tell you that text filters are simple — just click the dropdown and pick a condition. That's true right up until your filter runs, the results look plausible, and you're still missing rows you know should be there. I've been using Excel professionally for close to 20 years, and the number of times I've watched a capable person stare at a filtered column wondering what they did wrong (when the real problem was a trailing space they couldn't see) is higher than I'd like to admit. Text filters in Excel aren't difficult, but they're only as reliable as the data underneath them.

By the end of this guide, you'll be able to filter rows by conditions like "contains," "begins with," and custom wildcard patterns. You'll also know what to do when the filter lies to you. Before you start, make sure your dataset has a header row in row 1, no fully blank columns breaking up your data, and at least one text column you want to filter. If you're brand new to Excel and haven't worked with structured data before, the Excel for Beginners starter guide covers that foundation first.

Excel column dropdown open showing the Text Filters submenu highlighted
Text Filters appear in the AutoFilter dropdown whenever Excel recognizes a column as containing text data.

Step 1: Turn On AutoFilter and Apply Your First Text Filter

How to Enable AutoFilter and Open the Text Filter Menu

Click any cell inside your data range (not a blank cell outside it). Then go to the Data tab and click Filter, or press Ctrl+Shift+L. Small dropdown arrows will appear in each header cell. That's AutoFilter turned on.

Click the dropdown arrow on whichever column holds the text you want to filter. Hover over Text Filters in the menu. If you see Number Filters instead, that column has a data type problem: likely a formula outputting a number stored as text, or mixed content in the cells. Excel reads the column and decides which filter type to show you. Fix the data type first, or skip ahead to the troubleshooting section below.

If your dropdown arrows don't appear, click any cell inside the data range before enabling AutoFilter. Clicking outside the range causes Excel to apply filters to an empty or misread area.

Using "Contains," "Begins With," and "Ends With" Conditions

The most useful starting conditions are Contains, Begins With, and Ends With. Say you're working with an employee roster and you want to see everyone in a role that includes the word "Manager." Click the job title column dropdown, go to Text Filters → Contains, type Manager, and click OK. Excel will show every row where that cell contains that string anywhere: "Account Manager," "Senior Manager," "Manager of Operations," all of it.

"Begins With" and "Ends With" work the same way, just anchored to the start or end of the cell. These are useful for filtering codes, prefixes, or structured text fields. The Equals condition is stricter: it requires a character-perfect match, so use it only when you know exactly what's in the cell.

Step 2: Build a Custom Text Filter Using Wildcards and Multiple Conditions

Once you've applied a basic contains filter, the Custom AutoFilter dialog is the next step up. This is where text filters get genuinely useful for complex datasets.

Combining AND/OR Conditions in the Custom AutoFilter Dialog

Go to Text Filters → Custom Filter. The dialog gives you two condition rows connected by either And or Or. Use And when a cell must meet both conditions (for example: begins with "NW" and does not contain "pending"). Use Or when either condition is enough ("contains East" or "contains West" to pull two sales regions at once). This is how you handle filtering for multiple text values without building a separate helper column.

Using Wildcards (* and ?) to Match Partial Text Patterns

Inside any text filter condition, you can use two wildcards. The asterisk (*) stands in for any number of characters. The question mark (?) stands in for exactly one character.

  • proj* matches "project," "projection," and "proj-2024"
  • ?-code matches "A-code" or "B-code" but not "AB-code"

In practice: the asterisk tells Excel not to care what comes after a string, just find it. The question mark holds a single unknown character's place.

For more on organizing your data before filtering it, the introduction to sorting and filtering in Excel is worth reading alongside this guide.

Why Your Excel Text Filter Isn't Working — and How to Fix It

This is the section most tutorials skip. You've set up your filter correctly, the dialog looks right, and you still get incomplete results or no results at all. Nine times out of ten, the data is the problem, not the filter.

Cleaning Hidden Spaces With TRIM Before Filtering

Trailing spaces are invisible, which is exactly what makes them so effective at ruining your afternoon. If a cell contains "Chicago " (with a space after it) and your filter is looking for "Chicago," Excel won't match them. It's not being picky — it's being literal.

I TRIM my lookup values by default on any imported data. It takes two seconds. In a helper column, use =TRIM(A2) and copy it down, then paste as values over the original column before you filter. This applies especially to data that came in from a CSV, a copy-paste from another system, or an export from software that pads its fields.

Never filter a column before TRIMming it if the data came from an external source. Trailing spaces in imported data are nearly universal and will silently drop rows from your results.

The guide to sorting and filtering on Mac also covers some of the formatting quirks that show up differently on that platform.

When to Use the FILTER Function Instead of AutoFilter

AutoFilter has one structural limitation: it doesn't update when your data changes. You filter, you get results, someone adds a row, and your filter is already stale. In Excel 365, the dynamic array FILTER function solves this. The syntax looks like this:

=FILTER(A2:C50, ISNUMBER(SEARCH("Manager", B2:B50)))

It returns a live array of matching rows that recalculates automatically with each change. SEARCH is case-insensitive here, which is usually what you want. If you need true case-sensitive filtering, swap SEARCH for FIND.

As of 2026, the FILTER function is available in all current Microsoft 365 subscriptions and Excel 2021. If you're on an older license, AutoFilter and Advanced Filter are your options.

Common Mistakes With Excel Text Filters

Three mistakes come up constantly, and all of them are fixable once you know what to look for.

  1. Expecting case-sensitive results from AutoFilter. Excel text filters are not case-sensitive. "CHICAGO," "Chicago," and "chicago" all match the same filter condition. If you need case-sensitive filtering (say, product codes where case actually matters) you'll need a formula-based approach using FIND inside the FILTER function, since FIND (unlike SEARCH) distinguishes uppercase from lowercase.
  2. Forgetting to clear an active filter before applying a new one. If a filter is already running on your dataset, a second filter stacks on top of it. You're filtering the filtered results, not the full data. Always check the status bar at the bottom of the screen: it'll say something like "17 of 203 records found" when a filter is active. Use Data → Clear to reset before starting fresh.
  3. Trusting the row count in the status bar as if it represents everything. That count only reflects visible rows. It's easy to run a filter, see "43 records," and not realize there's another active filter two columns over narrowing the results further.

If you take one thing from this article: clean your data before you filter it, not after it fails. TRIM first, filter second. Every time.

Frequently Asked Questions

Why does Excel show Text Filters instead of Number Filters on my column?

Excel determines filter type based on what it finds in the column. If any cells contain text (including numbers formatted as text, or formula outputs that return a text value) Excel will show Text Filters instead of Number Filters. Check for cells where numbers are left-aligned (a sign they're stored as text) and reformat or re-enter them as true numbers.

Why is my Excel text filter not returning any results?

The most common cause is trailing or leading spaces in the cell data: the filter condition looks for "Chicago" but the cell actually contains "Chicago " with a trailing space. Run TRIM on the column before filtering. Also check whether another filter is already active on a different column, which would be limiting the visible rows before your new filter runs.

Can Excel text filters be case-sensitive?

AutoFilter cannot perform case-sensitive filtering: it treats uppercase and lowercase as identical. For true case-sensitive filtering, use the FILTER function combined with FIND, which distinguishes between "ABC" and "abc" where SEARCH does not. This approach requires Excel 365 or Excel 2021.

What's the difference between AutoFilter and the FILTER function in Excel?

AutoFilter is a manual UI tool: you apply it, it hides non-matching rows, and it stays static until you change it. The FILTER function (Excel 365 and later) returns a dynamic array of matching rows as a formula result, so it updates automatically whenever the source data changes. Use AutoFilter for one-time analysis; use the FILTER function when you need results that stay current.