Table of Contents
Wildcard characters can be used with the FILTER function in Google Sheets to search for specific text or data in a row or column. Wildcards allow you to search for text or data that contains a specific character, phrase, or pattern. Common wildcards include the asterisk (*) for any number of characters, the question mark (?) for a single character, and square brackets ([ ]) for a range of characters. Using wildcards with the FILTER function can help you quickly find and highlight the data you need.
You can use the following formula in Google Sheets to use a wildcard character within a FILTER function:
=FILTER(A2:C11,search("avs",A2:A11))
This particular formula will filter the cells in the range A2:C11 to only contain the rows where the value in A2:A11 contains “avs” somewhere in the cell.
The following example shows how to use this formula in practice.
Example: Use FILTER with Wildcard in Google Sheets
Suppose we have the following dataset in Google Sheets that contains information about various basketball players:
Now suppose that we would like to filter the dataset to only contain rows where the value in the Team column contains “avs” somewhere in the name.
We can type the following formula into cell A13 to apply this filter:
=FILTER(A2:C11,search("avs",A2:A11))
The following screenshot shows how to use this formula in practice:
Notice that the only rows displayed in the filtered dataset contain the string “avs” somewhere in the team name.
To search for a different string, simply replace “avs” with another string in the formula.
For example, we can use the following formula to filter for rows where the team name contains “ets” somewhere in the name:
=FILTER(A2:C11,search("ets",A2:A11))
The following screenshot shows how to use this formula in practice:
Now the only rows displayed in the filtered dataset contain the string “ets” somewhere in the team name.