Searching a massive spreadsheet for specific text can be annoying when you do it manually. There isn’t an exact IF CONTAINS Google Sheets function. 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.
Syntax for IF CONTAINS in Google Sheets
To perform this operation, we will be using a combination of two formulas:
- IF – to check whether a logical expression is true or false
- REGEXMATCH – to check whether the text matches the data in a cell or simple text strings.
Syntax for the IF Formula
=IF(expression, if_true, if_false)
- expression: defines the value or the cell address as true or false.
- If_true: defines the value to be displayed if the expression is true.
- If_false: optional and can be used to define the value (to be displayed if the expression returns a false value).
Syntax for the REGEXMATCH Formula
=REGEXMATCH(text, expression)
- Text: the text or the cell address to be tested against the expression.
- Expression: the expression which the string will be tested against.
A Note on SEARCH Function
Note: Instead of REGEXMATCH, you may also want to use the SEARCH function: =SEARCH(search_for, to_search)
- Search_for: represents the value to look for within the to_search argument
- To_search: the text that the function will search
Now that we have discussed the formulas we’ll use, let’s build a Google Sheets formula to solve “if cell contains.”
Related: Check out our Google Sheets Formula Cheat Sheet!
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. To discover whether a single string exists in a cell, follow to use the IF and the REGEXMATCH function:
1. In this example, we are using sales data. We wish to show either an affirmative or negative, depending on whether the cell range B2:B10 contains the word “Pencil.” To do this, we create a new column first, including our formula. Select the cell to put the formula. In this case, we’ve chosen cell D2.
2.Type in the IF formula first. You can use the autofill feature to input the formula correctly.
3. Enter the REGEXMATCH formula directly after the open bracket in the IF formula. First, we put in the cell address as the first argument (here, cell B2). Next, we input the keyword to check for matches after the comma. In this case, the word “Pencil.” Finish this formula by adding a closing bracket.
4.Input the true and false values to finish the formula. Add a comma, followed by:
- value for the true argument inside quotation marks
- another comma
- the value for the false argument, with both placed in quotation marks (e.g., Yes, No).
Note: 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 automatically apply the formula to all the cells in the column.
How to Check for a Single String of Text (from Multiple) with Google Sheets “IF Cell Contains Text” and REGEXMATCH
If you want to check if a cell contains one of several strings of text, you can use IF 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:
1. Create a new column first (including our formula).
2.Type the IF formula first. You can use the autofill feature to input the formula correctly.
3. Enter the REGEXMATCH formula after the open bracket in the IF formula. In the cell address, include the first argument in the REGEXMATCH function which should point to the text you want to evaluate (here, cell B2).
4. Next, include the keyword to check for matches after the comma. In this case, it’s “Pencil | Pen.” Finish this part of the formula by inputting a closing bracket.
5. Add another comma and write the value for the true argument inside quotation marks. 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.
6. Finally, add the closing bracket and press Enter.
7. 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 automatically apply the formula to all the cells in the column.
Our formula will return a “Yes” for any cell that includes either the word “Pencil” or “Pen” (as shown above).
Example of Using Google Sheets “IF Column Contains” with SEARCH
This method works similarly to the REGEXMATCH method. However, 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:
- 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. Note: Make sure you close the brackets of the SEARCH formula, or the formula may return an error.
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 write the value for false. Ensure the strings are typed within quotation marks.
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: Instead of the “No” response, you may 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). The FALSE value in the IF formula is redundant, so you can skip adding it if you prefer.
The overall formula in our example would look like this instead:
IF(SEARCH(“Pencil”, B3), “Yes”)
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 “Yes” if the cell contains the character “P.” If not, it will return a #Value! Error.
Is There an IF Contains Function in Google Sheets?
There isn’t a specific IF CONTAINS function in Google Sheets. However, you can combine the IF and the SEARCH (or REGEXMATCH) function to determine 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 whether 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 IF Contains Function
We hope this article helps explain IF CONTAINS Google Sheets formula alternatives. We explained how to use the IF function, how to use the REGEXMATCH function, and how to combine the two for the desired result.
There are plenty of formulas you can combine with your IF CONTAINS Google Sheets results – feel free to get creative with it! If you’re looking for more help in mastering Sheets, why not check out one of Coursera’s accredited certification courses?
