How to Use the CONTAINS String Operator in Google Sheets

Reading Time: 4 minutes

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
Selecting dataset for creating a named range to use in CONTAINS operator in Google Sheets
Figure 1: Selecting dataset for creating a named range
  • Give a suitable name to the data range. Here I am giving it a Dataset.
  • Click on Done to complete the process of naming.
Creating a named range for CONTAIN operator in Google Sheets
Figure 2: Going to Named Ranges to name our data range

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.
Using the named range as the first parameter of CONTAINS operator
Figure 3: Using the named range as the first parameter

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:
Sentences containing ‘long’ is the output of the CONTAIN operator in Google Sheets
Figure 4: Sentences containing ‘long’ is the output

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 :
Google Sheets CONTAINS strings operator for a partial match
Figure 5: Example of sentences containing ‘rig’ somewhere in the sentence

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.
A case-sensitive query output of the CONTAINS operator in Google Sheets
Figure 6: Example of ERROR output due to case sensitivity ‘right’
  • 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.
CONTAINS string operator in Google Sheets for case-sensitive match
Figure 7: The query CONTAINS operator in Google Sheets with a case-sensitive 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:
Query to select sentences that DO NOT contain a particular string
Figure 8: Example of sentences NOT containing the string ‘a’.

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

X
Hire expert data analysts on-demand. Get 30 Days Free Trial