The like string operator is used with the WHERE clause in the QUERY function. It enables the use of wildcards in Google Sheets Query.
Syntax
=QUERY(data, query, [headers])
data – The range of cells to perform the query on.
query – The query to perform.
The value for query
must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.
headers – [Optional] The number of header rows at the top of data
.
Sample Usage
=QUERY(Dataset,“Select A where A like ‘A%’ “)
//Retrieves all values starting with “A” from the range “Dataset”
=QUERY(Dataset, “Select A where A like ‘A%y’ “)
//Returns all values starting with “A” and ending with “y”
=QUERY(Dataset, “Select A where A like ‘Ma__’ “)
//Retrieves all values starting with “Ma” and having exactly 4 letters
Learning the use of the LIKE string operator in Google Sheets Query.
- How to use the LIKE string operator
- Ex. 1: Query cells starting and ending with specific letters
- Ex. 2: Query data ending with a specific letter
- Ex. 3: Like string operator with underscore
- Ex. 4: Like string operator with underscores for end letters
Background
The LIKE string operator in Google Sheets helps you determine whether a specific character string matches a specified pattern. Using the LIKE operator you can examine whether there are words present containing specific letters or not, inside a cell.
Use Like String Operator in Google Sheets in 3 Steps
Step 1: Setting up the data range
- Selecting the data range:
- We will use a dataset for querying. For ease of querying, we will name the range so we do not need to select the data range every time.
- Click Ctrl + A (Windows) or Cmd+A (Mac) to select the entire data range.
- Go to Data
- Select Named Ranges

- Naming the data range :
- Give a suitable name to your data range. Here I am giving it a Dataset.
- Click on Done to complete the process of naming.

Step 2: Using QUERY function of Google Sheets to start querying
- Type =QUERY( in a cell to start querying.
- For the first parameter, select the dataset of interest.
- Here, we will use Dataset, our named range.

Step 3: Using LIKE string operator in Google Sheets Query function
- The second parameter of the query function is the query you want to perform.
- Here, I have used this query to select names starting with A. The formula below will return all names starting with A.
=QUERY(Dataset,"Select A where A like 'A%' ")
- The output of this query will be as shown in the following image.

Example 1: Using LIKE string operator in Google Sheets for querying cells starting with a letter and ending with another letter
- You can use the operator to select names starting with a certain letter and ending with a certain letter.
- Here, the ‘%’ operator is used to specify that there is no limit to the number of letters between the first and last letters.
- The syntax given below returns all the values starting with “A” and ending with “y”.
=QUERY(Dataset, "Select A where A like 'A%y' ")
- The output of the query will be:

Example 2: Using LIKE string Operator in Google Sheets for querying data ending with a specific letter
- If you want to query data where you want names ending with a specific letter, use something like the following. This syntax returns all values ending with the letter “y”.
=QUERY(Dataset, "Select A where A like '%y' ")
- The ‘%’ means, there can be any number of letters or numbers, but the last letter should be y.
- The output of the query will be as shown in the image below.

Example 3: Using LIKE string operator in Google Sheets with _ (Underscore)
- If you want only a specific number of letters between or after or before a letter, use Underscore to specify the number of letters.
- If you want only one letter, use 1 underscore, 2 for two letters and so on. Use :
=QUERY(Dataset, "Select A where A like '_e_' ")
Figure 10: Query to have a letter before and after ‘e’
- This query will select 3 letter names that have the letter ‘e’ in between. The output will be :

Example 4: Using LIKE string operator in Google Sheets with _ (Underscore) for end letters
- You can select names starting with a set of defined letters, and ending with a specific number of letters using the operator with an underscore. Use :
=QUERY(Dataset, “Select A where A like ‘Ma__’ “)
Figure 12: Query to select names starting with ‘Ma’ and having exactly 2 letters after it
- The query will select all names which start with the letters Ma and then have exactly 2 letters after them. The output will be :

Conclusion
The LIKE String Operator in Google Sheets is helpful to look out for desired substrings. If you want to have a detailed view of Google Sheets Query, you can go through the official documentation from here.
See Also
You can checkout other equally good articles on Google Sheets here.
Delete Empty rows in Google Sheets: In this article we would see how to delete empty rows in Google Sheets using two different ways.
How to group rows in Google Sheets: We will learn how to group rows in Google Sheets. We will also have a look at nested grouping.