IF CONTAINS in Google Sheets - Easy!

Google Sheets
/
January 19, 2024

Introduction

When managing a list of data in Google Sheets you may be wondering whether it is possible to filter it based on a CONTAINS filter. Even though there isn’t an IF CONTAINS function in Google Sheets there is, fortunately, an easy work around. 

For the Google Sheets tip of this week, we look at how to combine the REGEXMATCH function with IF statements to achieve the same returns of the IF CONTAINS filter in SQL.

Did you know?

Are you looking to transform your spreadsheets into a data powerhouse that automatically updates with data from all your tools?

Get started with Dokin, and automate your spreadsheets. It's easy and free.

Try for free

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.

Enjoyed this article?

Check out our blog or our help center for more.
Dokin, the data platform of modern business teams
Try for free

Jacopo Proietti

Co-founder @Dokin
Jacopo, a co-founder of Dokin, boasts 8 years in finance, having worked as a finance manager at Ogury and head of Financial Planning and Control at BlaBlaCar.
His passion for data integration led to the creation of Dokin, a game-changer for modern business teams. With customizable templates and built-in data connections, Dokin allows modern CMOs and CFOs to streamlines data reporting across Google Workspace applications.

More Stories

Google Sheets
Digital Marketing Metrics & KPIs: How to Track Them for Success
Productivity tips
How Google Gemini Might Reshape SEO
Productivity tips
Choosing the Best Funnel.io Alternatives in 2024