How to Use Filter with Wildcard in Google Sheets (3 Examples)

Sometimes while working on a dataset, you need some specific data or characters. Finding or sorting those particular data in the dataset manually is very tiring and time-consuming at the same time. This article covers the solution to this problem. In this article, we will learn how to filter data with wildcard in Google Sheets.


A Sample of Practice Spreadsheet

You may copy the spreadsheet below and practice yourself.


What Are Wildcard Characters in Google Sheets?

Wildcards are special characters that can represent other characters very specifically. We usually use the following wildcard characters in Google Sheets.

  • Asterisks(*): This character represents all characters that remain before or after the specified position.
  • Question Mark(?): It represents a single character in the specified location.
  • Tilde(~): This wildcard character instructs that the following character should be used as a regular character rather than a wildcard.

3 Suitable Examples of Using Filter with Wildcard in Google Sheets

The dataset below contains Writer’s Name, Book Name, and Rating. Suppose you need to filter the dataset for the book ratings of specific writers. Here, we will demonstrate how to use the filter tool with wildcard to do that in Google Sheets. Let’s start.

google sheets filter wildcard


1. Filter Data with Partial Match

Here we will filter the dataset for partial matches in the first, last, and middle names of the writers in the dataset.

1.1 First Name

Suppose, you need to collect the rating of all the books whose writers’ name starts with Andrew. You can use the Filter tool with the Asterisk(*) wildcard after Andrew as Andrew* to get the required result. The steps are mentioned below.

📌 Steps:

  • First, select the header row from the dataset, the range is B4:D4.

Filter Data with Partial Match in Google Sheets

  • After that, the filter icons will be visible in the header row.

  • Next, click the filter icon in cell B4, and a drop-down list will pop up.

  • Next, select Filter by condition >> Text contains from the drop-down list as below.

  • After that, enter Andrew* into the Value or formula box and click OK.

  • As a result, the output will be as below.

Read More: How to Use Wildcard in Google Sheets (3 Practical Examples)


1.2 Last Name

Here we will collect the ratings of all the books whose writer’s name ends with Gill. You can filter the dataset using the Asterisk(*) wildcard before Gill as *Gill to do that.

📌 Steps:

  • First, select anywhere in the dataset and go to Data >> Create a filter as earlier. Then select Text contains from Filter by condition.
  • Next enter *Gill into the Value or formula box to get every value that contains Gill at the end, and click OK.

Filter Data with Partial Match in Google Sheets

  • Lastly, the output will be as below.


1.3 Middle Name

Now assume you want to collect the rating of all the books whose writer’s middle name is Stuart. You can use the  Asterisk(*) wildcard before and after Stuart as *Stuart* to filter them.

📌 Steps:

  • First, apply Filter to the dataset, click on the filter icon in the Writer’s Name cell and then select Text contains from Filter by condition by following the earlier steps.
  • Next enter *Stuart* into the Value or formula box, to get every value that contains Stuart in the middle and click OK.

Filter Data with Partial Match in Google Sheets

  • After that, you will get the following result.


2. Extract Data with Specific Number of Characters

Suppose, you need to collect the rating of all the books whose writer’s name starts with A, ends with w, and contains exactly 4 characters in between. You can use the Question Mark(?) wildcard to filter the dataset for that.

📌 Steps:

  • First, select Text contains from Filter by condition by following the earlier steps.
  • Then enter A????w into the Value or formula box to get the value that contains A in the first place, w in the last place and there will be four more letters in the middle. Next click OK to filter the dataset based on these criteria.

Extract Data with Specific Number of Characters in Google sheets

  • Finally, the filtered dataset will look as follows.


3. Data Containing Wildcard Characters

Now suppose some book names contain an asterisk (*) character which is a wildcard character itself. So how to filter those books from the dataset?  Well, you can use the Tilde(~) character before the wildcard character to do that while filtering the dataset. Then the filter tool will consider the asterisk in the dataset as a simple character.

📌 Steps:

  • First, click on the filter icon in the Book Name cell and select Text contains from Filter by condition by following the earlier steps.
  • Then enter Direction~* into the Value or formula box to get the value that contains wildcards at the end as a random value and click OK as before.

Data Containing Wildcard Characters in Google Sheets

  • Consequently, you will see the desired result as follows.


Things to Remember

  • The FILTER function doesn’t support wildcard characters. So you can use the Filter tool instead to filter data easily with wildcard criteria.
  • You can save the filtered output so that you don’t have to filter the dataset repeatedly.

Conclusion

In this article, we explained how to filter data with wildcard in Google Sheets with some practical examples. Hopefully, this article will help you to solve problems regarding this issue. Please let us know in the comment section if you have any further queries or suggestions. You may also visit our OfficeWheel blog to explore more Google Sheets-related articles.


Related Articles

Afrina

Afrina

This is Afrina Nafisa. Currently working as a Technical Writer at OFFICEWHEEL.COM. I have done my graduation from Ahsanullah University of Science and Technology. As I am eager to learn more and more currently my motive is to make myself better every day with my work so that I can make better content for all the readers.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo