How to Use the CONTAINS String Operator in Google Sheets

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.

Background

The Contains string operator in Google Sheets helps you find a case-sensitive string of characters with an exact match in the dataset. Using the Contains operator, you can determine whether or not a particular string is present in a cell.

3 Steps to use the CONTAINS String operator in Google Sheets

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 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.
  • Here, I have used this query to select names starting with A :

=QUERY(Dataset, “Select A where A contain ‘long’ “)

Figure 4: Query to select sentences containing ‘long’

  • The output of this query will be :
Sentences containing ‘long’ is the output
Figure 5: Sentences containing ‘long’ is the output

Example 1: Using CONTAINS string operator in Google Sheets for a partial match

  • You can use the CONTAINS operator to select sentences containing partial matches to specific strings. 

=QUERY(Dataset, “Select A where A contain ‘rig’ “)

Figure 6: Using the CONTAINS operator for partial match

  • The output of the query will be sentenced in which the ‘rig’ string is present somewhere, like :
Example of sentences containing ‘rig’ somewhere in the sentence
Figure 7: Example of sentences containing ‘rig’ somewhere in the sentence

Example 2: CONTAINS string operator in Google Sheets for case sensitive match

  • The CONTAINS operator is a case sensitive match finder in Google Sheets Query.
  • If the case doesn’t match exactly, it will show an error of ‘#NA’.
  • Let’s say you write the query as shown below :

=QUERY(Dataset, “Select A where A contain ‘rig’ “)

Figure 8: Query to select data with string in capital letters

  • Since there is no substring in the entire dataset that contains the word ‘RIGHT’, with all letters capital, the query output will be :
Example of ERROR output due to case sensitivity
Figure 9: Example of ERROR output due to case sensitivity
  • However, if you write the query as :

=QUERY(Dataset, “Select A where A contain ‘RIGHT’ “)

Figure 10: Query to select data with string as present in the dataset

  • Since the CONTAINS operator is case sensitive and there are sentences that contain ‘right’ as the substring, the output will be :
Example of sentences containing ‘right’
Figure 11: Example of sentences containing ‘right’

Example 3: How to Use Does Not Contain in Substring Match in Query

If you want to select sentences that do not contain a particular string, use:

=QUERY(Dataset, “Select A where NOT A contains ‘a’ “)

Figure 12: Query to select sentences that DO NOT contain a particular string

  • The output of the query will remove all the sentences which will contain ‘a’ somewhere in the sentence, as shown below :
Example of sentences NOT containing the string ‘a’.
Figure 13: 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