.png)
Does Google Sheets Have a "Contains" Function?
Well, not really. The IF CONTAINS statement is typical of SQL queries, and that is not available as a formula in Google Sheets. However there is an easy work around for it. We can achieve the same of an IF CONTAINS statement by combining the IF and REGEXMATCH functions.
The IF function:
Returns a value if a set condition is true and another value if the set condition is false.
Syntax:
=IF(logical_expression, value_if_true, value_if_false)
The REGEXMATCH function:
Defines whether a piece of text matches a regular expression.
Syntax:
=REGEXMATCH(text, regular_expression)
Where:
Text: is the cell or string where we are looking up for a set value
Regular_expression: is the regular expression item we are looking for in the cell or string.
How to Find the Cell Containing Text in Google Sheets
Step-by-Step Guide:
As an example, let’s take a look at the use case I had this week. I was extracting a list of leads from the Koala platform. They provide a list of companies that you filter based on size, revenue, industry and tech stack.
For this instance I wanted to find companies that are customers of HubSpot and are using the Google Suite. However, their tech stack filter is a OR filter, not an AND filter. Hence, to filter the list of companies down I need to make an extract of HUbSpot customers, and then further filter down based on the companies that have G Suite in the list of their tech tools.
The extract from Koala looks like this:
I have a Lead column, country, industry and their tech stack. The tech stack cell is a concatenation of strings, representing the list of tools used by each company in the list.
I can now use my IF and REGEXMATCH to find which companies in my list of HubSpot customers use the G Suite.
It’s easy!
The function is going to be:
=IF(REGEXMATCH(D9, “G Suite”), TRUE, FALSE).
This functionally basically means that:
- I’m looking for the G Suite value in the tech tools list in the tech stack cell of each lead
- If the lead uses G Suite, the function returns TRUE
- If the lead doesn’t use the G Suite (or better, if G Suite is not contained in the list of tools in its tech stack cell), the function returns FALSE
Mastering this functionality extends beyond simple text matching. It helps us handle several use cases with substrings, allowing for nuanced text analysis. For instance, it helps us identify if a cell contains text from a specific list, partial matches or specific patterns within a list of leads or of customers, contracts etc. This combination gives you the power of an SQL IF CONTAINS function within Google Sheets.
Counting Cells with Specific Text in Google Sheets
Now that we know to use the combination of IF and REGEXMATCH it’s easy to do many things with it.For example if we wanted to count the number of cells which have a specific text (e.g. “G Suite”) in their tech stack, we can simply use the COUNTIF function.
Conclusion
In conclusion, even though "Google Sheets contains" functionality does not exist, there’s an easy workaround, made possible by combining the REGEXMATCH function with IF statements. Whether you're filtering data based on specific text, counting occurrences, or analyzing partial matches, this filter allows you to achieve it easily.
.png)
