In this tutorial, you will learn how to check if a cell contains a particular substring in Google Sheets.
Checking if a Cell Contains a Substring in Google Sheets
Quick Navigation
Have you ever wanted to check if any text in a range contains a particular word or character?
For example, given a list of colors, you want to know which cells contain the word “Red” or “Blue”.
Google Sheets comes with functions like FIND, SEARCH, and REGEXMATCH that can help find substrings in a given text.
In this guide, we will explain a few different methods you can use in Google Sheets to check if a cell contains a particular substring.
How to Search for a Substring in Google Sheets
Here’s how to search for a substring in Google Sheets.
Step 1
First, let’s select an empty cell to place the SEARCH function.
In this example, we want to find all colors that have “Red” as a substring.
Step 2
Next, we’ll type the SEARCH function to check if the adjacent cell contains our desired substring.
The SEARCH function will return the character position where the first occurrence of the substring was found.
Step 3
You may have noticed that the SEARCH function returns a #VALUE! error when no substring is found. We can wrap our function with an IFERROR function to catch these cases and return an empty string.
Step 4
We can also use the REGEXMATCH function to check if a cell contains a substring. The REGEXMATCH function returns either TRUE or FALSE depending on whether the provided cell has a substring that matches the provided regular expression.
In this example, we used the regular expression “(?i)red” to search for colors with the text “red”. The “(?i)” in the regular expression indicates that we can ignore case when finding matches.
Step 5
We can also use the FIND function to check if a cell contains a substring.
The main difference between SEARCH and FIND is that the FIND function is case-sensitive.
Summary
This guide should be everything you need to check if a certain cell contains a substring in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.