Google Sheets Filter: Find Cells Containing Text

Yes. In Google Sheets, select your data, choose Data > Create a filter, open the filter menu on the relevant column, then choose Filter by condition > Text contains and enter the text. For a separate results list that updates from a formula, use =FILTER(A2:C100, ISNUMBER(SEARCH("apple", B2:B100))) to return rows where column B contains “apple.”

What “contains text” means in a sheet

A contains filter looks for a word or character sequence anywhere inside a cell, not only a complete-cell match. If a product column contains “Apple juice,” “green apple,” and “Pineapple,” searching for “apple” can match all three because each includes those letters. It will not match “pear.” That makes the option useful for finding partial names, phrases, codes, or notes when you do not know the cell’s complete contents.

This differs from an exact match. A condition such as “Text is exactly” is intended to match a cell whose text is the specified value, rather than a longer value that happens to include it. For example, an exact “Apple” condition should not be used when you also want “Apple juice.” Choose the condition that reflects whether you need a substring or the whole cell value.

Filtering is also different from searching with your browser’s Find command. A filter hides rows that do not meet the condition, letting you inspect matching records as a group. It does not delete the hidden rows or change their contents.

Apply a contains filter from the menu

For a quick, interactive filter, make sure your data has a header row and that the columns form a continuous table. Then follow these steps:

Select a cell in the data range, or select the range you want to filter.
Choose Data > Create a filter. Filter controls appear in the header row.
Click the filter icon in the header of the column you want to search.
Choose Filter by condition in the menu.
Open the condition list, choose Text contains, type the search text in the value field, and confirm.

Sheets displays rows that meet the condition and hides the others. To see the full data again, open that column’s filter menu and choose Clear, or use Data > Remove filter to turn off the filter controls. Clearing a condition and removing the filter are not the same: the first removes that column’s restriction; the second removes the filtering interface from the range.

Check that Sheets has identified the intended range. If there are blank rows separating sections, or a title above the actual column headers, selecting the right table first can prevent the filter from treating a title or an incomplete block as part of the data.

Choose the right matching method

The filter menu offers several kinds of conditions, and the best one depends on what you mean by “contains.” Use Text contains for an ordinary substring, such as “north” in a location label. Use Text starts with or Text ends with when the matching text must be at one end of the cell. Use Text is exactly when the entire cell should match. These options are easier to maintain than a custom expression when you only need one simple condition.

For instance, if a Status column contains “Open – waiting for review,” a Text contains condition for “Open” can find it alongside a cell containing simply “Open.” An exact match would be more restrictive. Likewise, a search for “12” may match a longer text code such as “A12-B”; if that is not desired, use a more specific condition or a formula that checks the whole value.

Text conditions are for text content. If a column mixes numbers, dates, and labels, consider whether a numeric or date condition is more appropriate for those values. Formatting a number to look like text does not necessarily change its underlying value. A filter can therefore behave differently than expected when a column contains inconsistent data types.

Return matching rows with FILTER

Use the FILTER function when you want matching records displayed in a new area rather than hiding rows in the original table. In the example below, assume A2:C100 contains records, column B contains the descriptions, and row 1 contains headers. The formula returns every matching row in columns A through C:

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

SEARCH finds “apple” within each description, without requiring it to be the whole cell. ISNUMBER turns successful search results into TRUE values for FILTER. If B2 says “Green apple,” that row qualifies; if B3 says “Pear,” it does not. The formula returns the matching source rows in the formula’s location and updates when the source data changes. It does not hide or rearrange the original records.

Leave enough empty space below and to the right of the formula for the returned rows to expand. Existing content in that output area can prevent the results from appearing. If no rows match, FILTER returns an error; you can handle that case with IFNA. With records in A2:C100 and descriptions in B2:B100, this version displays a message instead:

=IFNA(FILTER(A2:C100, ISNUMBER(SEARCH("apple", B2:B100))), "No matching rows")

That message is a fallback, not a matching record. If the formula is part of a report or another calculation, keep the fallback text in mind so it is not mistaken for data.

Case sensitivity, multiple terms, and criteria

SEARCH is case-insensitive: “apple” can find “Apple.” If capitalization must matter, use FIND instead. Assuming the same A2:C100 record range and descriptions in B2:B100, this formula searches for the capitalized form “Apple” and returns rows where that exact case-sensitive sequence occurs:

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

Use multiple conditions when a match must also satisfy another requirement. For example, suppose column C contains a status, and you want descriptions containing “apple” only for rows marked “Open.” With records in A2:C100, descriptions in B2:B100, and status in C2:C100, use:

=FILTER(A2:C100, ISNUMBER(SEARCH("apple", B2:B100)), C2:C100="Open")

FILTER treats separate condition arguments as requirements that must all be true, so this formula applies an AND rule. In the filter menu, you can also add conditions to a column; check the available choices and whether they combine as “and” or “or” before relying on the result. If you need several alternative words, such as “apple” or “pear,” a formula using REGEXMATCH or separate SEARCH tests can express that logic, but the simple menu condition is less error-prone for a single phrase.

Filter controls, filter views, and shared sheets

A standard filter is convenient for a quick check, but it changes which rows are visible in the shared sheet. In a collaborative file, that can affect what other editors see. When you want a temporary or personal view of filtered records, use a filter view instead: choose Data > Create a filter view, then set the column’s condition in the filter-view controls. Filter views can be named and revisited, making them useful when different people need different ways to inspect the same source data.

Both approaches work on the source range rather than creating a second copy of the data. If you need a reusable output table for a dashboard, use FILTER in another area or on another sheet. If you only need to inspect a set of records briefly, the menu filter is usually simpler. Avoid sorting or editing filtered records without checking which rows are visible, especially when other people are working in the same sheet.

Common problems and how to fix them

No rows appear: Confirm the spelling, selected column, and filter condition. Check for leading or trailing spaces and unusual punctuation. Try a shorter distinctive substring to see whether the issue is the search term.
Unexpected rows match: A contains search can find the text inside a longer word or code. Use a more specific phrase, an exact-match condition, or a formula designed to check boundaries.
The filter button is missing: The range may not have a filter enabled, or you may be looking at a cell outside it. Use Data > Create a filter after selecting the table.
The formula reports an error: Check that the result area is clear, that the source and criteria ranges cover the same number of rows, and that the formula uses commas between arguments in the sheet’s locale.
Some records seem excluded: Look for blank rows that split the data, merged cells, inconsistent types, or values stored as numbers rather than text. Make sure the intended header and records are included in the filter range.

A particularly common misconception is that filtering removes data. A filter normally hides nonmatching rows; it does not erase them. If you plan to delete records, first remove or clear the filter and verify the complete range so that hidden rows are not overlooked.

Frequently Asked Questions

Can Google Sheets filter a cell that contains only part of a word?
Yes. Choose Filter by condition > Text contains and enter the sequence you want to find. It can match that sequence inside a longer cell value, which is why a short search may return more rows than an exact match would.

Is “Text contains” case-sensitive in Google Sheets?
For formula-based matching, SEARCH is case-insensitive and FIND is case-sensitive. If capitalization is important, use a formula with FIND or test the condition on representative values rather than assuming a broad text filter distinguishes uppercase and lowercase.

How do I filter rows when a cell contains one of several words?
For a small number of alternatives, use a FILTER formula with separate matching tests combined as an OR condition, or use a regular-expression approach. If you only need one word or phrase, the built-in Text contains condition is simpler.

Can I show matching rows without hiding the rest?
Yes. Put a FILTER formula in an empty area or another sheet. It returns matching rows as a separate result, while the source table remains visible. Make sure the formula’s output area has room to expand.

Why does Text contains return no results when I can see the text?
Check whether the text is in the column you filtered, whether the filter includes those rows, and whether the visible value contains spaces or different punctuation. Mixed data types can also matter; test with a distinctive part of the cell and inspect the underlying value if needed.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.