In this tutorial, we will learn How to check if a Cell Contains a value in Google Sheets
Google sheets if Cell contains text (How to find it )
- 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 for a specific 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 the below function.
- If Cell Contains with REGEXMATCH Function
- If Cell Contains with COUNTIF Function
- If Cell Contains with IF & SEARCH
By the end of this article, you will know How to check if a cell contains text in Google Sheets
Google Sheets if Cell contains text: Step-by-Step Guide
If Cell Contains Text 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”
Step 1:
Click on the cell where you want to get the results and type the function =REGEXMATCH
Now type the “( ” 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 the 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 Sheets. Select the cell which contains the result TRUE, Hover your mouse cursor to the right end bottom corner and drag it to the last cell.
Things to remember while using the REGEXMATCH
- The text your type inside the regular_expression area is case sensitive which means we type “Books” where B is Capital it will return FALSE in the result.
- It will only search for text given at the regular_expression, not a number.
How to use If Cell Contains Text 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
How to use If Cell Contains Text with IF & SEARCH
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; 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 needing additional formatting of cells 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 as an error as #VALUE!
- The main advantage of using this method is that it is not case-sensitive and numbers can be searched using this function.
How do you check if a Cell Contains a value in Google Sheets (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 Insert Spin Button in Google Sheets ( Easy Guide )
How to Freeze Rows in Google Sheets
How to Calculate Square Root and Cube Root in Google Sheets(Quick & Easy Guide)
How to use SUMSQ Function in Google Sheets(Quick & Easy Guide )
