Learning the use of the LIKE string operator in Google Sheets Query.
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 :
=QUERY(Dataset,”Select A where A like ‘A%’ “)
Figure 4: Query to select names starting with A
- The output of this query will be :
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.
=QUERY(Dataset, “Select A where A like ‘A%y’ “)
Figure 6: Using the like operator to select names starting and ending with specific letters.
- 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 :
=QUERY(Dataset, “Select A where A like ‘%y’ “)
Figure 8: Query to select data ending with letter 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 :
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 :
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.
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.