Are you tired of manually searching for specific text in a large spreadsheet? Look no further! In this guide, we will show you how to use clever combinations of formulas to automate the process using Google Sheets.
Syntax for the IF CONTAINS Formula in Google Sheets
To achieve this, we will be using a combination of two formulas:
IF– to check if a logical expression is true or false.REGEXMATCH– to check if the text matches the data in a cell or simple text strings.
Syntax for the IF Formula
The syntax for the IF formula is as follows:
=IF(expression, true_value, false_value)
expression: defines the value or cell reference as true or false.true_value: defines the value to display if the expression is true.false_value: (optional) defines the value to display if the expression evaluates to false.
Syntax for the REGEXMATCH Formula
The syntax for the REGEXMATCH formula is as follows:
=REGEXMATCH(text, expression)
text: the text or cell reference to test against the expression.expression: the expression to which the string will be compared.
A Note on the SEARCH Function
Instead of REGEXMATCH, you can also use the SEARCH function:
=SEARCH(search, within_text)
search: represents the value to search for within the within_text argument.within_text: the text that the function will search.
Now that we have discussed the formulas we will be using, let’s build a Google Sheets formula to solve the “if a cell contains” problem.
Related: Check out our Google Sheets Formulas Cheat Sheet!
Using the Google Sheets “IF a Cell Contains” Formula with REGEXMATCH (for a Single String)
REGEXMATCH in Google Sheets can find text if it contains a specific substring. To find out if a single string exists in a cell, follow the steps below to use the IF and REGEXMATCH functions:
-
In this example, we are using sales data. We want to display an affirmation or negation based on the presence of the word “Pencil” in the range of cells B2:B10. To do this, we first create a new column, including our formula. Select the cell where you want to put the formula. In this case, we have chosen cell D2.
-
Type the IF formula first. You can use the autofill feature to enter the formula correctly.
-
Next, enter the REGEXMATCH formula right after the opening parenthesis in the IF formula. First, put the cell address as the first argument (here, cell B2). Then, enter the keyword you want to search for after the comma. In this case, the word is “Pencil”. Finish this formula by adding a closing parenthesis.
-
Add the true and false values to finish the formula. Add a comma and then:
- a value for the true argument in quotes.
- another comma.
- the value for the false argument, all placed in quotes (e.g., Yes, No).
Note: To apply the formula to the entire column, click on the cell with the formula and drag it down (using the handle in the bottom-right corner). This will automatically apply the formula to all cells in the column.
How to Check If a Single Text String (Among Multiple) is Present with Google Sheets “IF a Cell Contains Text” and REGEXMATCH
If you want to check if a cell contains any of the multiple text strings, you can use IF with REGEXMATCH.
In this example, we are using sales data. We want to display “Yes” or “No” based on the presence of the words “Pencil” or “Pen” in the range of cells B2:B10. Here’s how:
-
First, create a new column (including our formula).
-
Type the IF formula first. You can use the autofill feature to enter the formula correctly.
-
Next, enter the REGEXMATCH formula after the opening parenthesis in the IF formula. In the cell address, include the first argument in the REGEXMATCH function that should point to the text you want to evaluate (here, cell B2).
-
Then, include the keyword to search for after the comma. In this case, it’s “Pencil|Pen”. Finish this part of the formula by adding a closing parenthesis.
-
Add another comma and write the value for the true argument in quotes. Add another comma and write the value for the false argument. Wrap both of them in quotes. In this case, we use Yes and No.
-
Finally, add the closing parenthesis and press Enter. To apply the formula to the entire column, click on the cell containing the formula and drag it down (using the handle in the bottom-right corner). This will automatically apply the formula to all cells in the column.
Our formula will return “Yes” for any cell containing either the word “Pencil” or the word “Pen” (as shown above).
Example of Using the Google Sheets “IF a Column Contains” Formula with SEARCH
This method works similarly to the REGEXMATCH method. However, the false entry of the IF formula will return a #VALUE! error to indicate a missing substring. Here are the steps to follow:
-
Click on the cell where you want to write the formula and start typing the syntax for the IF formula. After the opening parenthesis, type SEARCH. Enter the term you want to search for in quotes, write a comma, and then click on the cell reference. Note: Make sure to close the parentheses of the SEARCH formula, otherwise, it may return an error.
-
After closing the parenthesis of the SEARCH formula, add a comma and type the value to display if the formula returns true. Add another comma and write the value for false. Make sure the strings are entered in quotes.
-
Press Enter to execute the formula. You can also drag the cell’s handle down to execute the formula in other cells within the column.
Note: Instead of the “No” response, you may receive a #VALUE! error. When you hover over this error, it will indicate: “In the evaluation of SEARCH, unable to find Pencil in Binder” (or whatever word you are searching for). The false value in the IF formula is redundant, so you can remove it if you wish.
The overall formula in our example would look like this: IF(SEARCH("Pencil", B3), "Yes")
Frequently Asked Questions
How to check if a cell contains a formula in Google Sheets?
You can use the ISTEXT formula to determine if a cell contains a formula. The ISTEXT formula returns TRUE if the specified cell contains a formula and FALSE otherwise.
The syntax for ISTEXT is: =ISTEXT(cell)
An example formula would be: =IF(ISTEXT(A1), "Yes", "No")
This will return “Yes” if there is a formula and “No” if there isn’t.
How to check if a cell contains a specific character?
To check if a cell contains a specific character, you can use the SEARCH function. For example, if we want to check if a cell contains the character P, we would use the formula:
=IF(SEARCH("P",B2),"Yes", "No")
This will return “Yes” if the cell contains the character “P”. Otherwise, it will return a #VALUE error.
Is there a CONTAINS IF function in Google Sheets?
There is no specific CONTAINS IF function in Google Sheets. However, you can combine the IF and SEARCH (or REGEXMATCH) functions to determine if a cell contains a specific value.
How to check if a cell contains a certain string of characters?
Here are the formulas you can use to check if a cell contains a specific string of characters:
=IF(REGEXMATCH(text, expression), true_value, false_value)
=IF(SEARCH(search, within_text), true_value, false_value)
Conclusion on the Google Sheets CONTAINS IF Function
We hope this article has helped you understand the alternatives to the Google Sheets CONTAINS IF formula. We have explained how to use the IF function, how to use the REGEXMATCH function, and how to combine them to achieve the desired result.
There are many formulas you can combine with your Google Sheets CONTAINS IF results – feel free to get creative! If you’re looking for more help in mastering Sheets, why not check out one of the accredited certification courses by Crawlan.com?
