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!
Video Guide: How to Check for Text in a Cell in Google Sheets
I made this short video showing how I use my IF Cell Contains function to show me whether a cell contains text.
Next, I’ll break down the formula so you can duplicate it.
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.
=IF(REGEXMATCH(B2, "Pencil"),"Yes", "No")
Note that “pencil” could be replaced by a helper cell. You could check cell B2 based on the contents of another cell, and the formula will also work. Here’s how that would look:
Just remember to add dollar signs in front of the letter and number of your helper cell. That ensures that, when you fill down in Google Sheets, the helper cell doesn’t automatically update in your data range like the target cell that you’re checking.
This method also allows you to try for partial matches. Fill in “app” instead of “apple” and the formula will just check that partial text.
Related: Here’s a full guide on how to count cells with text in Google Sheets.
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.
Want to check whether cells contain an error? There’s a function especially for that. Read my guide to the IFERROR function.
