Learning the use of CONTAINS string operator in Google Sheets QUERY function.
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 contain 'long' ")
//This returns all the values of all cells that contain the word “long” from the range named “Dataset”.
Background
The Contains string operator in Google Sheets helps us find a case-sensitive string of characters with an exact match in the dataset. Using the Contains operator, we can determine whether or not a particular string is present in a cell.
How to use the CONTAINS String operator in Google Sheets
Step 1: Setting up 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
- Give a suitable name to the data range. Here I am giving it a Dataset.
- Click on Done to complete the process of naming.
Step 2: Using the 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 instead of manually selecting the data range.
Step 3: Using CONTAINS string operator in Google Sheets for an exact match
- The second parameter of the query function is the query you want to perform.
- As an example, we’ll use the following formula. This formula will return all cell values that have the word “long” in them.
=QUERY(Dataset, "Select A where A contain 'long' ")- The output of the CONTAINS string operator of the above formula is as shown below:
Example 1: Using CONTAINS string operator in Google Sheets for a partial match
- We can use the CONTAINS operator in Google Sheets to select cells containing partial matches to specific strings. The formula below is one example of the CONTAINS string operator to retrieve a partial match.
=QUERY(Dataset, "Select A where A contain 'rig' ")- The output of the query will be sentenced in which the ‘rig’ string is present somewhere, like :
Example 2: CONTAINS string operator in Google Sheets for case sensitive match
- The CONTAINS operator in Google Sheets is a case-sensitive match finder in Google Sheets Query.
- If the case doesn’t match exactly, it will show an ‘#NA’ error.
- Let’s use the formula below as an example.
=QUERY(Dataset, "Select A where A contain 'RIGHT' ")- Since there is no substring in the entire dataset that contains the word ‘RIGHT’, with all letters capitalised, the query will return an #N/A error as shown below.
- However, if we write the query as :
=QUERY(Dataset, "Select A where A contain 'right ")- Since the CONTAINS operator is case sensitive and there are sentences that contain ‘right’ as the substring, the query will give the correct output.
Example 3: How to Use Does Not Contain in Substring Match in Query
To use the CONTAINS operator to retrieve values that do not contain a particular string, we use the NOT clause.
For example, if we want to select sentences that do not contain a particular string, in this case the letter “a”, we’ll use the following formula.
=QUERY(Dataset, "Select A where NOT A contains 'a' ")- The output of the query will remove all the sentences which will contain ‘a’ somewhere in the sentence, as shown below:
Conclusion
You can use the CONTAINS operator to look out for strings or substrings in a dataset. You can also have a case-sensitive or a partial match using the CONTAINS string operator in Google Sheets. If you want to know more about the CONTAINS string operator in Google Sheets, you can go through the documentation here.
See Also
How to Create a Drop-Down List in Google Sheets: Learn how to create drop-down lists in Google Sheets.
How to use Dark Mode in Google Docs, Sheets, and Slides: Learn to switch on dark mode in Google Docs, Sheets and Slides.
How to Split Text to Columns in Google Sheets: Learn how to create drop-down lists in Google Sheets.
How to Import a CSV File into Google Sheets: Learn how to import a CSV file into Google Sheets