Google Sheets Filter If Text Contains: Steps and Options

To filter Google Sheets rows when a cell contains particular text, select your data, choose Data > Create a filter, open the filter icon in the relevant column, then choose Filter by condition > Text contains and enter the text. For a live formula-based result instead, use =FILTER(A2:C5,ISNUMBER(SEARCH("urgent",B2:B5))) with the matching data layout described below.

Use the built-in “Text contains” filter

The built-in filter is usually the quickest way to temporarily show matching rows without changing the underlying data. It works well when you want to inspect, sort, or share a working view of a table. Start with a data range that has a header row, such as a list of tasks with columns for region, status, and task description.

Click a cell in the data range. If the data is isolated, Google Sheets will generally detect the range automatically. To avoid including unrelated cells, select the exact range first.
Choose Data > Create a filter. Filter icons appear in the header cells.
Click the filter icon in the column you want to search, such as Status or Description.
Choose Filter by condition, then select Text contains from the condition list.
Type the substring to find in the value box and click OK.

Rows that do not satisfy the condition are hidden from view; they are not deleted. A funnel icon indicates that a filter is active. To see all rows again, open the column filter and clear the condition, or use Data > Remove filter to turn filtering off.

What “text contains” matches

“Contains” means the entered characters can appear anywhere within the cell’s text. If a Status cell says “Urgent: order supplies,” searching for order can match it just as searching for Urgent can. You do not need to enter the entire cell value. This makes the condition useful for descriptions, notes, email addresses, product names, and other fields where values are not identical.

It is a substring search, not a word-boundary search. A search for art can match “article” or “carton” as well as a cell containing the separate word “art.” The condition is also not a regular-expression field: characters such as an asterisk should not be assumed to work as wildcards. If you need a pattern, use a formula or a regular-expression function instead.

Text matching in the filter is generally not sensitive to capitalization, so an entry such as urgent can find “Urgent” as well as “URGENT.” If capitalization must distinguish a match, use a case-sensitive formula approach rather than relying on the standard condition.

Return matching rows with the FILTER formula

A filter control changes which rows are displayed in the existing table. The FILTER function creates a separate, dynamically updating result from a range. This can be more convenient for a report, dashboard, or second sheet where you want matching records to appear without manually adjusting the source table’s filter.

Suppose A1:C5 contains headers in row 1 and four records in rows 2 through 5. Column A is Region, column B is Status, and column C is Task. The statuses are “Urgent,” “Waiting,” “urgent,” and “Closed,” in that order. With this layout, enter the following formula in an empty cell outside the source range:

=FILTER(A2:C5,ISNUMBER(SEARCH("urgent",B2:B5)))

The result is the North and East records, because rows 2 and 4 have a status containing “urgent.” SEARCH looks for the text within each status, and ISNUMBER converts a successful search into TRUE for FILTER. In Google Sheets, SEARCH is not case-sensitive, which is why both “Urgent” and “urgent” match here. Keep the result area clear so the returned rows have room to expand.

If no records meet the condition, FILTER returns an error indicating that no matches were found. That differs from the built-in filter, which simply leaves no visible data rows. Check that the search text is present as written and that the data and condition ranges cover the same number of rows.

Match more than one phrase

Sometimes a row should appear if its text contains either of two terms. In the same A1:C5 example, with statuses in B2:B5, use this formula in a clear area:

=FILTER(A2:C5,((ISNUMBER(SEARCH("urgent",B2:B5)))+(ISNUMBER(SEARCH("review",B2:B5))))>0)

This returns the North, West, and East records: the first and third statuses contain “urgent,” and the second contains “review.” Adding the two TRUE/FALSE tests acts like an OR test here; a value greater than zero means at least one term matched. For a condition that requires both terms in the same cell, multiply the two tests instead of adding them. With the same layout, =FILTER(A2:C5,ISNUMBER(SEARCH("urgent",B2:B5))*ISNUMBER(SEARCH("order",C2:C5))) keeps only rows whose status contains “urgent” and whose task contains “order.”

For a simple one-column filter, it is often clearer to use the built-in condition. Formula-based OR and AND logic is more useful when the criteria span columns or when you want the result to feed another calculation. If you combine multiple conditions in a formula, use parentheses to make the intended logic easy to read and maintain.

Choose the right approach for the job

Use the built-in filter to inspect a table, quickly narrow a view, or let someone choose conditions interactively. It does not create a second copy of the matching rows.
Use FILTER with SEARCH when you want a separate result that updates as the source data changes. It is also handy for combining conditions or referencing the result elsewhere.
Use Find when you only need to locate a particular occurrence, not hide nonmatching records. Find searches for text but is not a row-filtering tool.
Use a filter view when you want to save a particular filtered arrangement or work with filters without changing the view for everyone using the spreadsheet. Look under Data > Filter views to create or manage views; availability and options can depend on the current Sheets interface and sharing context.

Filters are especially useful on larger tables because they keep the original records together while reducing visual clutter. If you need to export just the matching rows or build a report that remains visible beside the source data, a formula result may be a better fit. Remember that a formula result is not a new, independently edited copy: it is calculated from the source and updates when its inputs change.

Common problems and how to fix them

If a row you expect is missing, check which column’s filter icon you opened. The condition applies to that column, not to every cell in the row. Also check for spelling differences, extra spaces, punctuation, or a search term that is longer than the text in the cell. A cell containing a number may not behave like a text description, so confirm that the values in the selected column are the kind of content you intend to search.

If a formula returns no results, verify that the search range and return range start and end on corresponding rows. For example, A2:C5 and B2:B5 are aligned; using B1:B5 would include the header and create a range-size mismatch. Put the formula outside the range it reads, because placing a spilled result over its own source can cause a circular-reference or expansion problem.

Another frequent confusion is thinking that hiding rows removes data. It does not: clearing or removing the filter reveals the records again. Be cautious when copying only visible rows, though, because the result you copy may not represent the whole table. If collaborators are filtering a shared sheet, a filter view can help avoid changing the ordinary view for other people.

When regular expressions or case sensitivity matter

For more advanced pattern matching, Google Sheets has the REGEXMATCH function. It can test a cell against a regular expression, such as alternatives separated by a vertical bar. Regular expressions are more powerful than “Text contains,” but they also require more care: characters such as a period or question mark can have special meanings in a pattern.

For example, with statuses in B2:B5 and records in A2:C5 as above, =FILTER(A2:C5,REGEXMATCH(B2:B5,"(?i)urgent|review")) returns rows whose status contains either term, with (?i) making the pattern case-insensitive. Without that flag, regular-expression matching is case-sensitive. For a straightforward case-sensitive literal search, the FIND function is another option; unlike SEARCH, it distinguishes uppercase and lowercase. Choose these tools only when the built-in text condition or simpler formula does not meet the requirement.

Frequently Asked Questions

Can Google Sheets filter text that appears anywhere in a cell?
Yes. Choose Filter by condition > Text contains and enter the substring. It can match text at the beginning, middle, or end of a cell.

Is “Text contains” case-sensitive in Google Sheets?
The standard filter condition is generally case-insensitive. For a formula, SEARCH is also case-insensitive, while FIND is case-sensitive.

Can I filter for two possible words?
The built-in condition is best for one text term at a time. To match either of two terms in a formula result, combine two SEARCH tests with an OR-style test, as in the example above.

Does filtering delete rows that do not match?
No. A filter hides nonmatching rows from the current view. Remove or clear the filter to display them again.

Why does my FILTER formula show an error?
Check that the return range and condition range have the same number of rows, that the formula is not blocking its own output, and that at least one row matches. A no-match result is different from a range-size error.

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.