Last Updated on October 31, 2023 by Jake Sheridan
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
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.
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.
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.
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.
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.
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.
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.
More ways to use text in Google Sheets:
- Add Bullet Points
- Insert Text Box
- Insert Special Characters
- Insert Symbols
- Insert Page Break
- Center Text
- How To Make Text Fit In Cell
- Make Text Vertical
- Change Case
- View Page Breaks
- Line Break In Google Sheets
- How To Capitalize
- How To Wrap Text
- How To Indent
- Count Cells with Text
- Fit Text to Cell
- TEXT Function
- Combine Text From Two Cells
- SUMIF Cells If Contains Text
- Add Text To Formula
- Indent Text
- Change Text To All Caps
- Spell Check in Google Sheets
- How to Use Spell Check
- Insert a Bullet List
- Make a Numbered List
- Insert Bullet Points
- Add a New Line In Cell
- Add Multiple Lines In One Cell
- Wrap Text In Cell
- Superscript In Google Sheets
- Make A Degree Symbol
- SUBSTITUTE Function
- How To Alphabetize