To filter Excel cells that contain a particular word or phrase, select your data, choose Data > Filter, open the column’s filter arrow, then choose Text Filters > Contains and enter the text. Excel keeps the matching rows visible and hides the others; it does not delete or change the underlying data.
What “contains text” means in Excel
The Contains option searches within a cell’s displayed value for the characters you enter. If a Status column contains In stock, Back order, and Pending, filtering for order will match Back order even though the search word is only part of the cell. The filter is generally not case-sensitive, so searching for back can match Back order.
This is different from filtering for cells that contain any text at all. Contains requires you to provide a search string; it is useful when you know the word or phrase you want to find. If your goal is to show every cell whose value is text, including varied words, use the alternative described below.
Excel applies a column filter to entire records. For example, filtering a table by Status hides each nonmatching row across all columns, not just the Status cell. The rows remain in the worksheet, and clearing the filter makes them visible again.
Apply a Contains text filter
- Check that each column has a clear header and that the records form one continuous range. Remove completely blank rows or columns from the middle of the data if they split the range.
- Click a cell in the range. You can first turn the range into an Excel table with Insert > Table if you want a structured dataset with filter arrows that expand as you add records.
- On the ribbon, choose Data > Filter. Excel adds a drop-down arrow to each header. On Windows, Ctrl+Shift+L toggles filters for a selected data range; on Mac, use the Data tab’s Filter command if your keyboard shortcut differs.
- Open the arrow in the column you want to search, point to Text Filters, and select Contains.
- Type the word or phrase in the filter box and select OK. Use the available second condition only if you want to combine criteria, such as containing one term but not another.
For example, if a Notes column contains “Call customer,” “Customer emailed,” and “Waiting for parts,” a Contains search for customer shows the first two rows. The order of the word in the cell does not matter. If the column contains numbers as well as text, the text search is for textual content and should not be treated as a general test for every nonempty cell.
Other text-filter choices
The Text Filters menu includes useful alternatives to Contains. Choose Begins With when the relevant text must be at the start of the cell, or Ends With when it must be at the end. For instance, Begins With INV- can isolate invoice codes that use that prefix. Does Not Contain is useful for excluding a term, while Equals is appropriate when the whole cell should match a value.
Use Custom Filter when one condition is not enough. You can combine two conditions with AND when both must be true, or OR when either may be true. A filter for a department containing Sales or Support can therefore show both groups without changing the source values. Check the selected AND/OR setting carefully: it controls whether Excel requires both criteria or accepts either one.
Text Filters is intended for text criteria. For numeric columns, Excel provides number-filter options such as greater than or between; for dates, it offers date-oriented filters. If the menu does not show the expected category, check whether the column contains mixed data types or whether the values that look like text are actually stored as numbers or dates.
Use wildcards for flexible matching
When searching text, wildcards can stand in for characters. An asterisk (*) represents any number of characters, including none; a question mark (?) represents one character. This can help when a label has several variations or when you only know part of a value.
- Searching for east* can match values beginning with east, such as East Region or east-02.
- Searching for *mail* can match text with mail anywhere in the cell, such as Email received.
- Searching for R?D can match three-character text such as R2D, where the middle character varies.
Wildcards are special characters, so a search for a literal asterisk, question mark, or tilde needs an escape tilde before that character: ~*, ~?, or ~~. If a search seems to return unexpectedly many or few matches, check whether the characters you typed are being interpreted as wildcards.
Filter cells that contain any text
A common related question is how to show every row where a column contains text, without specifying a word. AutoFilter’s Contains command is not a single “any text” test. Choosing a nonblank option can include numbers, dates, or other nonempty values too, so it may not meet the requirement when a column has mixed content.
For a quick check, inspect the column’s filter checklist and select only the text entries you want. This works well when there are only a few distinct values. If you need a repeatable test across mixed data, add a helper column and use ISTEXT to identify values Excel stores as text, then filter the helper column for TRUE. Remember that a number stored as text is text to Excel, even if it looks numeric.
Be aware of formulas that return an empty string. Although such a cell may look blank, Excel treats the formula result as text for ISTEXT, so it can be counted as text by a type test. A genuinely empty cell and a formula-produced empty string are not always equivalent in filters or formulas.
Use FILTER when you need a separate result list
AutoFilter is best when you want to view a subset in place. If you want a separate, automatically updated list of rows whose chosen column is text, the FILTER function can return matching records. FILTER is available in Microsoft 365 and Excel 2021 or later; it is not available in older perpetual versions such as Excel 2019.
Assume the records occupy A2:C7, the values to test are in B2:B7, and the rows are: 101 / In stock / 8; 102 / 12 / 4; 103 / blank / 6; 104 / Back order / 2; 105 / 0 / 1; and 106 / Pending / 3. Enter this in an empty area outside the source data:
=FILTER(A2:C7,ISTEXT(B2:B7),"No text entries")
The result is rows 101, 104, and 106: their Status values are text, while 12 and 0 are numeric and the third Status cell is blank. The formula spills into neighboring cells, so keep the destination area clear. If a source cell contains a formula returning an empty string, ISTEXT treats that result as text; use a nonempty check as well if those apparent blanks should be excluded.
Unlike AutoFilter, FILTER does not hide source rows. It creates a formula-driven output, which is convenient for reports or dashboards, but the output cannot be edited as if it were the original records. If you only need to inspect or copy matching rows, the regular column filter is often simpler.
Prepare the data and avoid common problems
Good data structure makes filtering more predictable. Keep one header row, put one record on each row, and avoid merged cells in the filter range. Use consistent labels where possible: “Back order,” “Backorder,” and “Back-order” are different text values, even if they mean the same thing to a person.
- No filter arrows appear: Click inside the data and choose Data > Filter. If Excel selected only part of the range, select the complete range first. A blank row can cause Excel to treat a block above or below it as a separate region.
- A value you expect is missing: Check for leading or trailing spaces, spelling differences, and text stored in another column. Contains finds the entered sequence, not a semantic match or a synonym.
- Numbers do not match a text search: A numeric value such as 125 is not necessarily stored as the text “125.” Check the cell’s actual type and use a number filter when you mean to compare numeric values.
- Other filters are still affecting the results: Excel combines filters across columns. Clear or review filters in the other headers before concluding that a matching row is missing.
Also distinguish filtering from Find. Find locates occurrences and can move the active cell to a match; a filter changes which records are currently displayed. Use Find for a one-off search, and a filter when you need to work with the matching rows as a group.
Clear or change a filter
To remove a filter from one column while leaving other column filters in place, open that column’s drop-down and choose Clear Filter From followed by the column name. To show all rows, use Data > Clear. This clears active filtering criteria but does not remove the filter arrows. Choose Data > Filter again if you want to turn the arrows off entirely.
Filtering only changes the view, but take care when copying, deleting, or editing a filtered range. A visible subset is not the same as a separate dataset, and commands can have effects beyond the rows you intended depending on how they are applied. If you need to preserve a distinct subset for editing or sharing, copy the visible results to another sheet and check the copied data before making bulk changes.
Frequently Asked Questions
How do I filter Excel for a word anywhere in a cell?
Open the column arrow, choose Text Filters > Contains, enter the word, and select OK. Excel displays rows where that sequence appears anywhere in the cell.
Can Excel filter cells that contain any text, not one specific word?
Not with the Contains criterion alone. Select text values from the filter checklist when practical, or use an ISTEXT helper column for a consistent test. A nonblank filter can include numbers and dates as well as text.
Is Excel’s Contains filter case-sensitive?
It is generally case-insensitive, so capitalization alone does not usually separate matches. If capitalization is meaningful to your task, use a method designed for case-sensitive comparison rather than relying on the standard text filter.
Why does my text filter miss a cell that looks like a match?
Check for extra spaces, a spelling or punctuation difference, a value stored as a number rather than text, or another active filter on the sheet. Also check whether wildcard characters in your search are acting as wildcards.
Does filtering delete the rows that do not match?
No. Excel hides nonmatching rows while the filter is active. Use the column’s clear-filter option or Data > Clear to display them again.
Can I do this in Excel for Mac or Excel for the web?
Yes. The basic process is also available in Excel for Mac and Excel for the web: apply a filter, open the column menu, and select the text condition. Ribbon placement and menu appearance can differ slightly, so the Data tab is the useful starting point.
Related
- Excel Custom Number Format: Hide Zeros Without Deleting Them
- Excel Custom Number Format for Thousands (k)
- Excel Custom Number Format: Add Leading Zeros
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Round Excel Cell Values: Formulas and Display OptionsTo round a number in an Excel cell and change the value used in calculations, use the ROUND…
- Excel Formula to Check Whether a Value Is in a ListWith list items in A2:A10 and the value to check in D2, use =IF(COUNTIF($A$2:$A$10,D2)>0,"Found","Not found"). COUNTIF checks whether…
- SUMPRODUCT Excel: Calculate a Weighted AverageIn Excel, calculate a weighted average with =SUMPRODUCT(A2:A5,B2:B5)/SUM(B2:B5), assuming A2:A5 contains the values and B2:B5 contains their weights.…
- Show Formulas in Excel: Keyboard Shortcut GuideTo show formulas in Excel for Microsoft 365 on Windows, press Ctrl+` (Ctrl plus the grave accent key,…
- Excel Filter in Table Format: How to Set Up and Use ItTo filter data in an Excel table, click a cell in the table and use the drop-down arrow…
- Calculate Age in Weeks in Excel with a Simple FormulaTo calculate completed age in weeks in Excel, put a birth date in A2 and enter =INT((TODAY()-A2)/7) in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.