How to Use LIKE String Operator in Google Sheets

Reading Time: 5 minutes

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.

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

  1. 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
Selecting dataset for creating a named range
Figure 1: Selecting dataset for creating a named range
  1. 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.
Going to Named Ranges to name our data range
Figure 2: Going to Named Ranges to name our data range

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.
Using the named range as the first parameter for querying
Figure 3: Using the named range as the first parameter

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 of LIKE string Operator in Google Sheets with string starting with a particular letter
Figure 4: Names starting with the letter A is the output

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 of LIKE operator of string starting and ending with different letters
Figure 6: Example of names starting and ending with a specific letter

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 of LIKE operator of string ending with a particular letter
Figure 9: Example of names ending with the letter ‘y’.

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 :
3 lettered words with ‘e’ in the middle
Figure 11 : 3 lettered words with ‘e’ in the middle

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 :
Example of selecting names starting with Ma and having exactly 2 letters after it
Figure 13: Example of selecting names starting with Ma and having exactly 2 letters after it

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.

shalinamigo

←
→

Leave a Reply