Google Sheets Filter Wildcards: What Works and How

Google Sheets’ basic filter conditions do not treat * or ? as wildcard characters: “Text contains” searches for those characters literally. To filter with wildcard-like patterns, use Filter by condition > Custom formula is with REGEXMATCH, or use wildcard criteria in functions such as COUNTIF.

How filtering works in Google Sheets

A filter hides rows that do not meet a condition; it does not delete them. In Google Sheets in a web browser, select the data range, including its header row, and choose Data > Create a filter. Filter icons appear in the header cells. Open the icon for the column you want to check, then choose a condition or values from the list.

For ordinary text searches, choose Filter by condition and a condition such as “Text contains,” “Text starts with,” or “Text ends with.” These options are often enough: “Text starts with” can find names beginning with North without entering a pattern. They are not wildcard syntax, though. Typing North* into “Text contains” asks Sheets to look for the literal text North followed by an asterisk; it does not mean “North and anything after it.”

Filters affect what is displayed in the shared sheet, so collaborators may see the same filtered rows. If you need a personal view that does not change the view for everyone, use Data > Filter views to create a filter view. Filter views are also useful for saving different sets of conditions for later.

Use a custom formula for wildcard-like matching

For patterns, the filter’s custom formula option can use REGEXMATCH. Suppose column A contains product names, row 1 has the header, and the records begin in row 2. Create a filter for the full table, open the filter icon in column A, select Filter by condition > Custom formula is, and enter:

=REGEXMATCH(A2, "^North.*")

This keeps rows whose column A value begins with North. The formula is evaluated for each row; the reference should point to the first data row and remain relative so it adjusts for subsequent rows. If your first record is in row 3, use A3 instead. In a custom formula, reference the column being filtered, even if the formula is entered through that column’s filter menu.

The regular expression parts have specific meanings: ^ anchors the match at the start of the text, North matches those letters, . matches a character, and * means zero or more repetitions of the preceding character or expression. Together, .* means any number of characters after North. To match North anywhere in the value, use:

=REGEXMATCH(A2, "North")

To match values that end in North, use North$ as the pattern. The dollar sign anchors the pattern at the end. These anchors matter: without them, a pattern can match a substring rather than the whole cell.

Translate * and ? into regular expressions

People often expect spreadsheet wildcards to work the same way in every feature, but Google Sheets has more than one pattern system. In regular expressions, an asterisk does not independently mean “any text,” and a question mark does not mean “one arbitrary character.” Use these common translations when creating a regular-expression filter:

Any number of characters: use .*. For example, ^Jo.* matches text beginning with Jo, including just Jo.
One character: use .. For example, ^A.c matches three-character text beginning with A and ending with c, such as Abc or A9c.
Either of two choices: use parentheses and a vertical bar. ^(Tea|Coffee)$ matches exactly Tea or Coffee.
Literal punctuation: put a backslash before a regular-expression special character. To find a literal period, use \.; to find a literal asterisk, use \*.

Regular expressions are powerful, but special characters can make a pattern behave differently from an ordinary text search. Characters such as a period, parentheses, brackets, plus sign, and asterisk have pattern meanings in many contexts. If a search term contains punctuation, escape characters that should be taken literally. Google Sheets uses RE2 regular expressions, which do not support every feature available in other regex engines, including lookaround assertions and backreferences.

Examples for common filter patterns

Here are practical patterns for a filter on column A, assuming the first data row is row 2. Enter each as the custom formula for the column’s filter condition:

Starts with “INV-”: =REGEXMATCH(A2, "^INV-.*"). The hyphen is literal here, and the pattern accepts any text after it.
Contains “blue” regardless of capitalization: =REGEXMATCH(A2, "(?i)blue"). The (?i) flag makes the match case-insensitive.
Exactly five characters: =REGEXMATCH(A2, "^.....$"). The anchors prevent longer values from matching.
Ends in .pdf: =REGEXMATCH(A2, "\.pdf$"). The backslash makes the period literal, and the dollar sign requires the ending.
Contains “tea” or “coffee”: =REGEXMATCH(A2, "(tea|coffee)"). Add anchors if the entire cell must be one of those words.

REGEXMATCH is case-sensitive unless you use the case-insensitive flag. Also check whether your pattern is supposed to match part of a cell or the complete cell. For an exact value, anchors such as ^ and $ make the intention explicit.

Wildcard criteria in COUNTIF and related formulas

If the goal is to count or summarize matching rows rather than hide nonmatching ones, criteria-based functions offer a different wildcard system. In Google Sheets, COUNTIF supports the asterisk for any number of characters and the question mark for one character. With product codes in A2:A100, for example, =COUNTIF(A2:A100, "INV-* ") would include a space before the closing quote and likely not match the intended codes. The correct criterion, with no extra space, is =COUNTIF(A2:A100, "INV-* ") only if codes end with a space; for ordinary INV- codes use =COUNTIF(A2:A100, "INV-*").

That function counts cells whose text starts with INV-. It does not filter the visible table. The distinction is useful: use a sheet filter when you want to inspect selected rows in place, and use a formula when you need a count or a separate result. Other criteria-based functions, such as SUMIF, also accept criteria patterns; check the function’s documentation and the shape of its ranges before adapting a formula.

In criteria strings for functions such as COUNTIF, a tilde escapes a wildcard character. For example, to count cells containing a literal asterisk, use =COUNTIF(A2:A100, "~*"). This tilde rule belongs to criteria syntax. It is not the same as escaping a regex character with a backslash in REGEXMATCH.

Return matching rows with FILTER

To display matching records in a separate area, use the Google Sheets FILTER function with REGEXMATCH as its condition. If the table is in A2:C100 and the text to test is in A2:A100, use:

=FILTER(A2:C100, REGEXMATCH(A2:A100, "^North.*"))

This returns rows from A2:C100 where the first column starts with North. The data range and condition range must cover the same number of rows. Unlike an interactive filter, FILTER produces a formula result elsewhere and updates as the source data changes. If no rows match, FILTER returns a #N/A error; you can wrap it in IFNA if you want a message instead. Be mindful that the output needs empty cells below and to the right so its results can expand.

For a simple text prefix, FILTER can also use a condition such as LEFT(A2:A100, 5)="North", but REGEXMATCH is more flexible when the pattern includes alternatives or variable text. Choose the simplest method that clearly expresses the rule, especially if someone else will maintain the sheet.

Common problems and how to fix them

The asterisk is treated literally: This is expected in “Text contains.” Switch to a custom formula with REGEXMATCH, or use a wildcard-capable function criterion if you are counting or calculating.
The formula filters the wrong rows: Confirm the row number in the reference matches the first data row, not the header. Keep the row reference relative so each record is checked against its own cell.
The pattern matches too much: Add ^ and/or $ to require a start or end position. A pattern without anchors can match text inside a longer value.
Uppercase and lowercase behave unexpectedly: REGEXMATCH is case-sensitive by default. Add (?i) at the beginning of the pattern when capitalization should not matter.
A literal dot or asterisk causes odd results: Escape it in a regex with a backslash, such as \. or \*. In COUNTIF-style criteria, use the tilde escape instead.
The formula returns an error for numeric data: REGEXMATCH is intended to test text. If a column contains numbers, consider converting the tested value to text with TO_TEXT, or use a numeric comparison where that better fits the task.

When a filter seems to show no matches, check the selected filter range as well as the pattern. A range that omits some rows will leave them outside the filter. Also verify that the data does not contain leading or trailing spaces; a pattern anchored at the start or end can reveal those inconsistencies. If appropriate, clean the source data with TRIM in a helper column rather than making an increasingly complicated pattern.

Frequently Asked Questions

Can I use * in Google Sheets filter by condition?
Not as a general wildcard in “Text contains.” That condition searches for the asterisk itself. Use “Custom formula is” with REGEXMATCH, such as =REGEXMATCH(A2, "^North.*"), to match North followed by any text.

What is the Google Sheets equivalent of *?
For REGEXMATCH, use .* to mean any number of characters. For criteria in functions such as COUNTIF, the asterisk itself is the wildcard for any number of characters. The correct form depends on the feature you are using.

What does ? mean in a Google Sheets wildcard?
In COUNTIF-style criteria, ? matches one character. In a regular expression, use a period (.) for one character; a question mark has a different regex meaning, where it makes the preceding item optional.

How do I make a Google Sheets filter case-insensitive?
For a REGEXMATCH custom formula, add the RE2 flag (?i) at the start of the pattern, as in =REGEXMATCH(A2, "(?i)blue"). This makes the text match without regard to uppercase or lowercase.

Is Google Sheets wildcard syntax the same as Excel?
Not across every feature. Google Sheets criteria functions such as COUNTIF use familiar * and ? wildcards, while a Sheets filter’s “Text contains” condition is not a wildcard pattern field. Regex-based custom formulas use regular-expression rules. Excel also has wildcard criteria in functions such as COUNTIF, but its filtering options and formula behavior can differ by feature and version.

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.