How do you check if a Cell Contains a value in Google Sheets? ( 5 Step Easy Guide )

In this tutorial, we will learn How to check if a Cell Contains a value in Google Sheets

Google Sheets comes with a lot of in-built features with functions to make our day-to-day work with Google Sheets easier. However, If we want to search a certain text inside a cell in our Google Sheets to check if a cell contains a value. Google Sheets doesn’t have any inbuilt function to do.

We can use some other functions to check if a Cell Contains a value in Google Sheets.

To check if a Cell Contains a value in Google Sheets you can use below function.

  • If Cell Contains with REGEXMATCH Function
  • If Cell Contains with COUNTIF Function
  • If Cell Contains with IF & SEARCH

Check if a Cell contains a value in Google Sheets : Step by Step Guide

If Cell Contains using REGEXMATCH Function

The easiest way to check if a Cell Contains a value in Google Sheets is by using REGEXMATCH Function.

REGEX MATCH Function will check a cell and provide the result based on the search query string.

  • True : If the query string matches the regular expression
  • False : If the query string does not matches the regular expression

SYNTAX

REGEXMATCH(text, regular_expression)
  • text – The text which will be checked againsts the regular expression.
  • regular_expression – The regular expression to check the text against.

Let’s go through the steps and use the REGEX MATCH Function

In the below example we will try to check a cell if it contains the Text “Books”

check if a Cell Contains a value in Google Sheets

Step 1: Click on the cell where you want to get the results and type the function =REGEXMATCH

Now type “( ” bracket after the function.

=REGEXMATCH (

Step 2: Enter the first parameter after the open bracket.

Type the cell where you want to search the value in the text area and type a comma at the end.

Step 3: Now in the regular_expression area type your search text.

Type your text that your want to search in the regular expression area after being separated by a comma and inside double inverted commas “your text ” close the function with bracket “)” without quotes as shown below.

Example : =REGEXMATCH(B2,”books”)

Step 4: Press Enter

As soon as you press enter you will get the result.

Step 5: Copy the same formula to other cells.

To get the result in all other remaining cells on your Google Sheet. Simply select the cell which contains the result TRUE, Hover your mouse cursor to the right end bottom corner and drag it till the last cell.

Things to remember while using the REGEXMATCH

  • The text your type inside the regular_expression area are case sensitive which mean we type “Books” where B is Capital it will return FALSE in result.
  • It will only search for text given at the regular_expression not a number .

How to use If Cell Contains with COUNTIF

In this method, we will use the COUNTIF function

Syntax

=COUNTIF(B2,“*text*”)
  • The above formula will search in the cells for provided text and if found will return a value of 1 or if not found it will return 0.
  • Ensure your provided text is within the Asterisks * which acts as a wildcard in the function which will allow it to search for the text everywhere.

This is how the formula works with the COUNTIF function

In the below example we are searching the cells to see if it contains “books”.This function is not case sensitive and will not search for numbers

Using both IF & Search functions we can search for a number or text within a cell

Syntax

=IF(SEARCH("books", B2) > 0, 1, 0)
  • Using this formula will check within the cells for your “text”.If found it will show the result as 1 or if not found it will return the result as “error showing #VALUE! “.
  • The benefit of using this function is that we can search for a number without the need of any additional formatting of cell as text .

This is how the function works :

In the below example we are searching in Cell B2 for the text “books”.

  • If the value is found in the cell the formula returns the result as 1
  • If the value is not found in the cell the formula returns the result an error as #VALUE!
  • This main advantage of using this method is that it is not case sensitive and numbers can be searched using this function .

Conclusion :

Perform a hands-on using the above-mentioned steps. We hope this tutorial on How to check if a Cell Contains a value in Google Sheets was helpful.

Related articles :

How to Sort a List Alphabetically in Google Docs

How to add Footnotes in Google Docs 2021

How to use autocorrect in Google Docs( Easy Guide 2021 )

How to Write Fractions in Google Docs: Easy Guide