Google sheets if cell contains formula

You are working in google sheets and can’t figure out if cell contains certain text or substring? 

You can use the REGEXMATCH formula in google sheets to check if cell contains.

If cell contains your text, then the result will be TRUE else it will be FALSE.

Here is a quick animation showing Google sheets if cell contains formula.

Unlike excel, you do not have an in-built function where you can check if a cell contains certain text. But, you can use many other formulas to solve this in google sheets.

Most popular is the REGEXMATCH formula. 

Also, you have the SEARCH formula. 

If you want to apply logical conditions on top of searching the text, you can use the IF formula. To calculate the number of matching cells, you can use the COUNTIF formula as well.

In this guide, I will show you complete information and formula for if cell contains in google sheets. 

Table of Contents

• REGEXMATCH formula for if cell contains in google sheets
• Google sheets if cell contains text
• 3 best alternative formula for if cell contains in google sheets
• Google sheets if cell contains number
• Google sheets if cell contains text then sum
• Google sheets if cell contains text from list
• Search multiple text in a cell

Google sheets if cell contains using REGEXMATCH

If you are looking to search for a text or string in a specific cell, one of the effective ways to find it is using the REGEXMATCH function.

This function will search a cell and check if the text exists. If it finds the text, it will return TRUE. If it doesn’t then it will return FALSE.

Here is the syntax for the same:

Syntax

=REGEXMATCH(text, regular_expression)

Input Parameters

text – the text (or Cell) to be checked against the regular expression
regular_expression – regular expression statement (or string to check)

Output

TRUE – If the search successful
FALSE – If the search did not find the required value

Let’s see a real-life example of using the REGEXMATCH function to find a text.

Example of REGEXMATCH formula

Google sheets if cell contains text using REGEXMATCH

Let’s say we have a long paragraph of text in a cell. Here is how it looks.

You want to check if the cell contains a text called “kingdom”.

1. Select the cell where you want to output the result

2. Type the formula =REGEXMATCH()

3. In the parentheses, type the cell reference where you want to check the test

4. Add comma

5. Type a double quote and inside the quotation write the text to search

6. Press enter to see the output

So the final formula will look like this:

=REGEXMATCH(A1, “kingdom”)

In the below table we have the final output from the formula.

The REGEXMATCH function is very handy. It helps you quickly search for the text inside a cell. 

But you need to know that it is case-sensitive. This means that if you search for “god” instead of “God”, the result will be FALSE. 

You can see this in the table below.

To avoid this kind of error scenario, you can convert the cell text to lowercase and then run the REGEXMATCH formula.

You can use the lower function to achieve this. Here is the updated formula.

=REGEXMATCH(lower(A1), “god”)

This will ensure that lowercase value is searched with lowercase text.

Here is how this will look

Note:- You can apply the lower() function to both parameters. 

=REGEXMATCH(lower(A1),lower("god"))

Alternative formula for if cell contains 

1. Query formula (fastest performance)
2. Search formula
3. CountIF formula

Google sheets if cell contains text using Query formula 

The query function is used to run a Google Visualization API query across data in google sheets. While the query function is used for complex data sets for complex calculations, we can use it to check if cell contains text in a google sheet.

Let’s say we have “hello world” written in A1. We want to run the query function in the B1 cell to search for the text “hello”.

The formula will look like this:

=IF(QUERY(A1, "where A contains 'hello'")<>"", TRUE, )

In this formula, A1 is the cell where you want to search.

Here is the result after applying this formula to the B1 cell.

Google sheets if cell contains text using Search formula

The search function in google sheets returns the position where the searched string is first found. It ignores the case while searching.

While the search function is used for more advanced use cases, we can use this to check if a cell contains a specific text. Here is how to do this.

=IFERROR(SEARCH("hello",A2)>0, FALSE)

Here we are first searching for the text “hello” in cell A2. This will output the position of the text “hello” in cell A2. In this case, it is position 1. 

Since we want to output TRUE or FALSE for finding the text, we can add the IFERROR function to wrap the SEARCH function inside. If the first condition inside the IFERROR function is met, then it will output TRUE. Else it will output what we define as the second parameter.

In the case of cell A3, when we check for the text “hello”, the SEARCH function did not find any position inside “Hi there”. So the IFERROR function will output the result as FALSE.

Google sheets if cell contains text using COUNTIF formula

And last but not the least method to find the text if google sheets cell contains is using the COUNTIF function.

Here is how you can use this function to search for text:

=COUNTIF(A2,"*hello*")

The first parameter is the cell where you want to search. The second parameter is the text you want to search surrounded by asterisks.

The * acts as a wildcard notion, so it will look for a match anywhere within the cell.

Here is the demo. The result will be in the form of 1 or 0.

Google sheets if cell contains number

The REGEXMATCH formula by default works for searching text or strings only. If you want to search for a cell that is a number field, then you have to first transform that cell from number to text field.

Either you can do that by selecting the cell and changing the field type from the menu or you can use a formula.

We use the “TO_TEXT()” formula for converting numbers to text.

Below you can see that A1 contains a number which is 5. If we apply the REGEXMATCH formula without transforming the number to text, you will get FALSE as the result.

As you can see the error says that it expects a text and not a number.

Now using TO_TEXT() formula, we can rewrite the REGEXMATCH formula like this

=REGEXMATCH(TO_TEXT(A1),"5")

And the result will now become TRUE.

And the result will now become TRUE.

And that’s how you search if google sheet contains a number.

Google sheets search for text in a range

Till now all the examples above were for searching a text in a single cell. If you want to know how to search in multiple cells, here I will show you a simple way to achieve this.

You can use the COUNTIF formula that we explained above. Instead of a single cell reference, you can have a range of cells.

For searching in a single cell, the formula looks like this

=COUNTIF(Cell,"*Value_To_Search*")>0

For searching in multiple cells, you can use

=COUNTIF(Cell_Range,"*Value_To_Search*")>0

Below is an example showcasing the same. 

For each cell in column B, column C will tell you whether it exists in the range of cells in the A column.

The result will be TRUE if the text exists in any of the cells in the range.

=COUNTIF(A2:A7,"*"&B2&"*")>0

If you want to search for static text instead of a dynamic cell reference, you can write the formula like this.

=COUNTIF(A2:A7,"*bird*")>0

Google sheets if cell contains text then sum

If you want to check how many times the text appears in the range, you can use the SUM formula on top of the COUNTIF formula. 

=SUM(COUNTIF(Cell_Range,"*Value_To_Search*"))

Here is an example.

=SUM(COUNTIF(A2:A7,"*is*"))

And the result looks like this

As you can see here, we found 4 uses of the word “is”.

Google sheets if cell contains text from list

Let’s say we have a list of email ids and we want to check if they are from valid email providers. The below table has the sample data

In this case, we are trying to check if cells in column A contain any of the values from the list in column B.

We can use the REGEXMATCH function with extra validations. Here is what the formula will look like.

=REGEXMATCH(A2,TEXTJOIN("|", TRUE, $B$2:$B$5))

This formula is case-sensitive. Meaning if the cell has GMAIL.com instead of gmail.com, this formula will result in FALSE. 

Use this modified formula which is not case-sensitive. 

=REGEXMATCH(A2,".*(?i)("&TEXTJOIN("|",TRUE,$B$2:$B5)&").*")

The TEXTJOIN function will join all cells in column B by adding a “|” operator between them. The “|” operator acts as an OR operator while joining the cells.

Also, you will prepend the (?i) operator to make the search case-insensitive. 

The formula in C1 will check if A1 contains text (“gmail.com” | “yahoo.com” | “aol.com” | “outlook.com”)

The result will look like this:

Google sheets if cell contains multiple texts from list

In the above example, we checked if any text from the list exist. 

But if you want to check for all text to be existing, you can do so using the CONCATENATE operator.

Let’s use the same example data set. Say you want to check if cell A1 contains “Gmail” and “steve”. The formula will look like this.

=REGEXMATCH(A2,"(?i)steve.gmail")

Here we concatenated both the words “steve” and “gmail”. Also we added(?i)before to make the search case-insensitive.

FAQ

Wrapping up

In this tutorial, you learned how to use functions and formulas to check if google sheets contain certain values. Also, you saw all the use cases like searching numbers, searching in a range of cells, searching from a list of cells, and so on. While I showed you functions like REGEXMATCH, SEARCH, QUERY, COUNTIF, etc, you can learn more about them individually.

Appendix

[1] REGEXMATCH formula in google sheets – Link

Further Reading

New to google sheets ? Start here

More related to Logical functions:

Logical functions in google sheets
Learn more about Google sheets Formulas.
Error handling in google sheets