Written by bolamarketing.com on October 31, 2023
Welcome to this tutorial where we’ll discover how to check if a cell contains a specific substring in Google Sheets.
Check If a Cell Contains a Substring in Google Sheets
Have you ever wanted to check if a text in a range contains a particular word or character?
For example, if you have a list of colors, you might want to know which cells contain the words “Red” or “Blue”.
Google Sheets offers functions such as FIND, SEARCH, and REGEXMATCH that can help you find substrings within a given text.
In this guide, we’ll explain multiple methods you can use in Google Sheets to check if a cell contains a specific substring.
How to Search for a Substring in Google Sheets
Here’s how to search for a substring in Google Sheets.
Step 1
First, select an empty cell where you want to place the SEARCH function.
In this example, we want to find all the colors that contain “Red” as a substring.
Step 2
Next, type the SEARCH function to check if the adjacent cell contains our desired substring.
The SEARCH function will return the position of the first character where the substring was found.
Step 3
You may have noticed that the SEARCH function returns a #VALUE! error when the substring is not found. We can wrap our function with an IFERROR function to capture 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 based on the match between the provided substring and 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 matching.
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 provide you with all the necessary information to check if a certain cell contains a substring in Google Sheets.
Feel free to make a copy of this spreadsheet example and test it for yourself.
For more informative articles and tips on Google Sheets and much more, visit Crawlan.com!
Article written by bolamarketing.com
