A Guide to If Contains Google Sheets Functions
Searching a massive spreadsheet for specific text can be annoying when you do it manually. There isn’t an exact IF CONTAINS formula in Google Sheets. However, you can use a clever combination of a few formulas to automate the process.
This article serves as a guide to help you use some formulas to search a cell to see whether it contains a specified value or not. Read on to master IF contains Google Sheets functions and formulas.
Table of Contents
Download A copy of Our Example spreadsheet
You can make a copy of our Example spreadsheet and follow along with this guide.
Syntax for IF Text Contains in Google Sheets
To perform this operation, we will be using a combination of two formulas, the IF and the REGEXMATCH formula. The IF formula checks if a logical expression is true or false, while the REGEXMATCH formula checks if the text matches the data in a cell or simple text strings.
Here is the syntax for the IF formula:
=IF(expression, if_true, if_false)
- expression – this argument defines the value or the cell address to see if it’s true or false.
- if_true – this argument defines the value to be displayed if the expression is true.
- if_false – this argument is optional and is used to define the value to be displayed if the expression returns a false value.
Here is the syntax for the REGEXMATCH formula:
=REGEXMATCH(text, expression)
- text – this is the text or the cell address to be tested against the expression.
- expression – this is the expression which the string will be tested against.
You may also want to use the SEARCH function instead of REGEXMATCH.
Here is the syntax for the SEARCH formula:
=SEARCH(search_for, to_search)
search_for – this argument represents the value to look for within the to_search argument.
to_search – this is the text that the function will search.
Now that we have discussed the formulas, let’s see them in action by building a Google Sheets formula to solve “if cell contains.”
Example of Using Google Sheets IF Cell Contains Text with REGEXMATCH for One String
REGEXMATCH in Google Sheets can find text if it contains a specific substring. Here are steps you need to follow to use the IF and the REGEXMATCH function to discover if a single string exists in a cell:
Step 1: In this example, we are using sales data. We wish to show either “Yes” or “No” depending on whether the cell range B2:B10 contains the word “Pencil.” To do this, we create a new column first, which will include our formula. Select the cell where you wish to put the formula. In this case, the cell is D2.
Step 2: Type in the IF formula first. You can use the autofill feature to input the formula correctly.
Step 3: Now, enter the REGEXMATCH formula right after the open bracket in the IF formula. First, we put in the cell address as the first argument. In this case, it’s cell B2. Next, we pet the keyword to check for matches after the comma. In this case, it’s the word “Pencil.” Finish this part of the formula by putting a closing bracket.
Step 4: Now, we will input the true and false values to finish the formula. Put another comma and write the value for the true argument inside quotation marks. Then, add another comma and write the value for the false argument. Put them both in quotation marks. In this case, we use Yes and No.
Step 5: Finally, add the closing bracket and press Enter.
To apply the formula to the whole column, click on the formula’s cell and drag it down using the dot in the bottom right corner. This will apply the formula automatically to all the cells in the column.
Example of Using Google Sheets IF Cell Contains Text with REGEXMATCH for One of Several Strings
If you want to check if a cell contains one of several strings of text, you can also use IF together with REGEXMATCH.
In this example, we are using sales data. We wish to show either “Yes” or “No” depending on whether the cell range B2:B10 contains the word “Pencil.” or “Pen”.
Here’s how:
Step 1: Create a new column first, which will include our formula.
Step 2: Type in the IF formula first. You can use the autofill feature to input the formula correctly.
Step 3: Now, enter the REGEXMATCH formula right after the open bracket in the IF formula. First, we put in the cell address as the first argument. In this case, it’s cell B2. Next, we put the keyword to check for matches after the comma. In this case, it’s “Pencil | Pen” Finish this part of the formula by putting a closing bracket.
Step 4: Now, we will input the true and false values to finish the formula. Put another comma and write the value for the true argument inside quotation marks. Then, add another comma and write the value for the false argument. Put them both in quotation marks. In this case, we use Yes and No.
Step 5: Finally, add the closing bracket and press Enter.
To apply the formula to the whole column, click on the formula’s cell and drag it down using the dot in the bottom right corner. This will apply the formula automatically to all the cells in the column.
Our formula will return a Yes for any cell with either the word Pencil or Pen, as shown above.
Example of Using Google Sheets IF Column Contains, Then with SEARCH
This method works similarly to the REGEXMATCH method. But, the FALSE extry for the IF formula will return an #VALUE error to indicate a missing substring. Here are the steps you need to follow:
Step 1 – Click on the cell where you wish to write the formula and start typing the syntax for the IF formula. After the open bracket, Type SEARCH. Enter the term to search for in quotation marks, write a comma, then click the cell reference. Make sure you close the brackets of the SEARCH formula, or the formula may return an error.
Step 2 – After the bracket is closed for the SEARCH formula, add a comma and type the value to show if the formula returns true. Add another comma and then write the value for false. Ensure the strings are typed within quotation marks.
Step 3 – Press Enter to execute the formula. You can also drag down the cell’s border to execute the formula in other cells in the column.
Note: You’ll notice that instead of the “No” response, you get a #VALUE error. When you hover over it, it will say: “In SEARCH evaluation, cannot find ‘Pencil’ within ‘Binder’,” or whichever word you are searching for. So, the FALSE value in the IF formula is redundant, and you can skip adding at all if you so choose.
The overall formula in our example would look like this instead:
IF(SEARCH(“Pencil”, B3), “Yes”)
IF Contains Google Sheets Frequently Asked Questions
How Do You Check if a Cell Contains a Formula in Google Sheets?
You can use the ISFORMULA function to determine if a cell contains a formula. The ISFORMULA function returns TRUE if the specified cell contains a formula and FALSE otherwise. The syntax for ISFORMULA is:
=ISFORMULA(cell) if_true, if_false)
An example of the formula would be:
=IF(ISFORMULA(A1), "Yes", "No")
This will return Yes if there’s a formula and No if there’s none.
How Do I Check if a Cell Contains a Character?
To check if a cell contains a character, you can use the SEARCH function. For example, if we wanted to check if a cell contains the character P, we would use the formula:
=IF(SEARCH("P",B2),"Yes", "No")
This will return a Yes if the cell contains the character P and a #Value! Error if it doesn’t.
Is There an IF Contains Function in Google Sheets?
There isn’t a specific IF CONTAINS function in Google Sheets. However, you can cleverly combine the IF and the SEARCH or REGEXMATCH function to find whether a cell contains a particular value.
How Do You Check if a Cell Contains a Certain String?
Here are the formulas you can use to check if a cell contains a specific string:
=IF(REGEXMATCH(text, expression), if_true, if_false) =IF(SEARCH(search_for, to_search), if_true,if_false)
Wrapping Up the Google Sheets Contains Function
We hope this article helped you better understand the Google Sheets if cell contains function. The REXEXMATCH method is better for most situations, but, they are both beneficial if you wish to find specific text in your spreadsheets. There are several formulas you can combine with your IF CONTAINS Google Sheets results. You can get pretty creative with it. Good luck!
