Excel Custom Text Filter: Find Matching Text in a List

Formulas & functions · Updated November 20, 2026

To use a custom text filter in Excel, open the filter arrow in a text column, choose Text Filters, select a condition such as Contains or Begins With, enter the text, and select OK. Excel displays only rows that match, without deleting the other data. You can also combine two conditions with And or Or to narrow the results or include alternatives.

What You’ll Need

  • A worksheet with a header row and a column containing the text you want to filter, such as product names, locations, or notes.
  • A continuous data range is easiest to filter. Remove completely blank rows or columns inside it, since they can interrupt Excel’s detection of the range.
  • Excel for Microsoft 365 on Windows. The same basic filter options are available in current Excel for Mac and Excel for the web, although the ribbon layout can differ slightly.

Step-by-Step

  1. Check the layout of your data. Keep one clear heading at the top of each column, with records beneath it. For example, a product list might have headers named Product, Category, and Notes. Avoid merged cells in the data area, and make sure there is no blank row splitting the records. The filter arrow belongs on a column heading, not on an individual data cell.
  2. Select a cell in the list. Click any cell within the data range. Excel will usually identify the surrounding block automatically. If the worksheet has nearby unrelated data, select only the range you intend to filter, including its header row. Selecting a range deliberately helps prevent Excel from adding unrelated columns or excluding part of the list.
  3. Turn on the filter arrows. On Windows, go to Data > Filter. You can also use Home > Sort & Filter > Filter. Excel adds a drop-down arrow to each heading in the selected range. If arrows already appear, filtering is on and you do not need to turn it on again. On Mac, use Data > Filter; in Excel for the web, use Data > Filter when that ribbon command is available.
  4. Open the arrow for the text column. Click the drop-down arrow in the heading whose values you want to check, such as Product or Notes. Choose Text Filters from the menu. If the column contains a mixture of text and numbers, Excel may show text and number filter options; select the type that matches the values you want to examine. The simple search box in the menu can find listed values, but Text Filters gives you a reusable rule such as Contains or Ends With.
  5. Choose a condition. Select the rule that describes your search. Equals matches a full value; Does Not Equal excludes a full value; Begins With and Ends With check the start or end of a cell; Contains finds text anywhere in the cell; and Does Not Contain excludes cells containing the text. For example, choose Contains to find product names that mention mug, including travel mug and mug set.
  6. Enter the text to match. In the Custom AutoFilter dialog box, type the word or phrase in the value field. For example, with Product as the selected column, choose Contains and enter mug. The filter is generally not case-sensitive, so mug can match Mug or MUG. Check the condition and spelling before proceeding; a stray space at the beginning or end of the search text can make the result seem unexpectedly narrow.
  7. Use a second condition when needed. The Custom AutoFilter dialog can hold two rules for the same column. Choose And when both rules must be true, such as Contains blue and Does Not Contain small. Choose Or when either rule may be true, such as Begins With north or Begins With south. Enter the second condition in the lower part of the dialog, then select OK. These two rules apply to one column; they do not create a general-purpose filter across any number of columns.
  8. Review the filtered rows. Excel hides rows that do not match; it does not erase them. Check the column heading for a filter indicator and look at the visible records to confirm they meet the rule. The row numbers may skip because some rows are hidden. You can still edit visible cells, so avoid pasting or deleting data until you are sure the right rows are showing.
  9. Clear or remove the filter when finished. To remove a rule from one column, open its arrow and choose Clear Filter From followed by the column name. To show all rows and remove active filters across the range, use Data > Clear. To remove the filter arrows themselves, choose Data > Filter again. Clearing a filter restores visibility; it does not change the records or undo other edits.

For a concrete example, suppose the Product column contains Blue mug, Mug set, Blue plate, and MUG brush. A Contains mug rule shows Blue mug, Mug set, and MUG brush. A Begins With mug rule shows Mug set but not Blue mug, because the word appears after the first characters in that value. This difference is useful when you want to find mentions anywhere rather than match a naming pattern.

Common Mistakes

  • Choosing the wrong rule. Contains mug and Equals mug do different jobs. Equals looks for the complete cell value mug, while Contains also finds phrases such as travel mug. If the result is empty, check whether the chosen condition is more restrictive than you intended.
  • Filtering the wrong column. The arrow menu applies to the column whose heading contains that arrow. If you want to search comments, open the Notes arrow, not the Product arrow. A correct search term in the wrong column can produce confusing results.
  • Assuming hidden rows were deleted. Filtered-out records remain in the worksheet. They can reappear when you clear the filter. Do not copy only a visible portion of a filtered range without checking what is selected, especially if you plan to share or replace the data.
  • Leaving an earlier filter active. A filter on another column can still restrict the visible rows. When the result looks incomplete, inspect every heading for a filter indicator and clear other active filters before judging the new rule.
  • Using a text rule for numeric data. If a column contains quantities, prices, or dates stored as actual numbers, use Number Filters or Date Filters where appropriate. Text filters may be unavailable or behave differently when Excel recognizes the values as numbers.
  • Expecting case-sensitive matching. Standard AutoFilter text conditions are not intended for case-sensitive searches. Contains mug can match uppercase or mixed-case versions. If capitalization itself matters, use a different approach, such as a helper column with a case-sensitive formula, rather than relying on the filter dialog.

Tips

  • Use wildcards for patterns. In AutoFilter criteria, an asterisk (*) can stand for any number of characters, while a question mark (?) can stand for one character. For instance, a pattern such as mug* can match text beginning with mug. A tilde (~) before a wildcard treats that character as a literal, so ~* searches for an actual asterisk. If a wildcard pattern gives an unexpected result, try the built-in Begins With, Contains, or Ends With condition instead.
  • Build a clear And or Or rule. And makes the filter more selective because both conditions must be met; Or broadens it because either condition can be met. For instance, Contains blue AND Does Not Contain plate finds blue items except ones mentioning plate. Contains blue OR Contains red finds rows mentioning either color.
  • Use an Excel table for lists that grow. Select a cell in the data and choose Insert > Table, making sure the table has headers. Tables include filter arrows and generally expand when you add records directly below them. This can make repeated filtering more convenient than maintaining a manually selected range.
  • Keep the original values consistent. Extra spaces, inconsistent abbreviations, and different spellings can affect what you see. A Contains rule tolerates extra words around the search text, but it will not fix spelling differences. If a list comes from several sources, consider standardizing its values before drawing conclusions from the filtered results.
  • Use the search box for a quick one-off lookup. In the filter menu, the search box can narrow the checklist of values. Use it when you need to select a particular listed entry quickly. Choose Text Filters instead when the condition should match a fragment, prefix, suffix, or two criteria.

Troubleshooting

The Text Filters option is missing or unavailable. Confirm that you opened the arrow on the intended column and that the column contains text values. If filter arrows are absent, click inside the list and select Data > Filter. If the worksheet is protected, filtering may be disabled by its protection settings; ask the workbook owner to allow filtering or unprotect the sheet if you have permission.

No rows appear after applying the rule. Reopen the column menu and check the condition, search text, and any second rule. Temporarily choose Contains and enter a short, distinctive part of a value you can see in the original data. Check for spelling differences and leading or trailing spaces. Also inspect filters on other columns, since their conditions combine with the current one and may eliminate every row.

Some expected matches are missing. Verify that the records fall inside the filtered range and that there is no blank row separating them from the list. Confirm that the values are text rather than numbers or dates interpreted as numbers. If the text was imported, hidden characters or inconsistent spacing may be present; compare a suspect cell with a known match and clean the source data if needed.

The filter arrows disappeared or the range looks incomplete. Turn filtering back on with Data > Filter after selecting a cell in the data. If Excel filtered only part of the list, clear the filter, inspect for blank rows or columns, then select the complete range—including headers—and apply the filter again. For a list that regularly grows, converting it to a table can reduce range-selection problems.

The menu looks different on your device. On Mac and in Excel for the web, ribbon placement and dialog appearance can differ from Windows, but look for the arrow beside the column heading and the Text Filters choices. If the workbook is open in a viewer or an older application, open it in desktop Excel for Microsoft 365 to access the full set of filter controls.

Frequently Asked Questions

Does a custom text filter delete the rows that do not match?
No. Excel hides nonmatching rows while the filter is active. Clear the filter to show them again. The filter itself does not delete records.

Can I filter for text in the middle of a cell?
Yes. Open the column arrow, choose Text Filters > Contains, enter the text, and select OK. This finds the text wherever it appears within the cell.

Can I match either of two words?
Yes, if both conditions are in the same column. Choose Text Filters, set the first condition, select Or, and enter the second condition. For example, Contains blue OR Contains red returns cells mentioning either word.

Are Excel text filters case-sensitive?
No, standard AutoFilter text conditions are generally case-insensitive. If uppercase and lowercase must be treated differently, use a separate case-sensitive method rather than expecting the Custom AutoFilter dialog to distinguish them.

Can I filter text in more than one column?
Yes. Apply a rule to one column and then apply another to a different column. Excel shows rows that satisfy the active filters together, so rules on different columns narrow the displayed records.

How do I get all of my rows back?
Choose Data > Clear to clear active filters from the range, or use Clear Filter From in a specific column menu. The rows will reappear unless another filter remains active.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.