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
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”
Step 1: Click on the cell where you want to get the results and type the function =REGEXMATCH
Now type “( ” bracket after the function.
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
- 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
How to use If Cell Contains with IF & SEARCH
Using both IF & Search functions we can search for a number or text within a cell
=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 .
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 :