Excel is a powerful software application that enables users to manipulate and analyze data in a variety of ways. Formulas, especially formulas in Excel, are one of its most valuable features for extracting specific information from data sets. Excel has countless formulas, and they play a crucial role in performing various calculations and data manipulations.
The “if cell contains, then” function is one of the most commonly used formulas in Excel. Using this formula, the user can check if a certain condition within a cell is met, output a value, or perform an action based on the outcome of that check.
Moreover, for example, to highlight or delete cells that contain a certain keyword, you could use the “if cell contains” function along with other relevant Excel formulas to achieve the desired result. Understanding and effectively using formulas in Excel can significantly enhance your ability to work with and derive insights from data.
Excel Formula: If cell contains
=IF(ISNUMBER(SEARCH("abc",A1)),A1,"")
To test for cells that contain certain text, you can use a formula that uses the IF function together with the SEARCH and ISNUMBER functions. In the example shown, the formula in C5 is:
=IF(ISNUMBER(SEARCH("abc",B5)),B5,"")
If you want to check whether or not the A1 cell contains the text “Example”, you can run a formula that will output “Yes” or “No” in the B1 cell. There are a number of different ways you can put these formulas to use. At the time of writing, Excel is able to return the following variations:
- If cell contains any value
- If cell contains text
- If cell contains number
- If cell contains specific text
- If cell contains certain text string
- If cell contains one of many text strings
- If cell contains several strings
Using these scenarios, you’re able to check if a cell contains text, value, and more.
Explanation: If Cell Contains
One limitation of the IF function is that it does not support Excel wildcards like “?” and “*”. This simply means you can’t use IF by itself to test for text that may appear anywhere in a cell.
One solution is a formula that uses the IF function together with the SEARCH and ISNUMBER functions. For example, if you have a list of email addresses, and want to extract those that contain “ABC”, the formula to use is this:
=IF(ISNUMBER(SEARCH("abc",B5)),B5,""). Assuming cells run to B5
If “abc” is found anywhere in a cell B5, IF will return that value. If not, IF will return an empty string (“”). This formula’s logical test is this bit:
ISNUMBER(SEARCH("abc",B5))
Using “if cell contains” formulas in Excel
The guides below were written using the latest Microsoft Excel 2019 for Windows 10. Some steps may vary if you’re using a different version or platform. Contact our experts if you need any further assistance.
1. If a cell contains any value, then return a value
This scenario allows you to return values based on whether or not a cell contains any value at all. For example, we’ll be checking whether or not the A1 cell is blank or not, and then return a value depending on the result.
- Select the output cell, and use the following formula: =IF(cell<>””, value_to_return, “”).
- For our example, the cell we want to check is A2, and the return value will be No. In this scenario, you’d change the formula to =IF(A2<>””, “No”, “”).
- Since the A2 cell isn’t blank, the formula will return “No” in the output cell. If the cell you’re checking is blank, the output cell will also remain blank.
2. If a cell contains text/number, then return a value
With the formula below, you can return a specific value if the target cell contains any text or number. The formula will ignore the opposite data types.
Check for text
- To check if a cell contains text, select the output cell, and use the following formula: =IF(ISTEXT(cell), value_to_return, “”).
- For our example, the cell we want to check is A2, and the return value will be Yes. In this scenario, you’d change the formula to =IF(ISTEXT(A2), “Yes”, “”).
- Because the A2 cell does contain text and not a number or date, the formula will return “Yes” into the output cell.
Check for a number or date
- To check if a cell contains a number or date, select the output cell, and use the following formula: =IF(ISNUMBER(cell), value_to_return, “”).
- For our example, the cell we want to check is D2, and the return value will be Yes. In this scenario, you’d change the formula to =IF(ISNUMBER(D2), “Yes”, “”).
- Because the D2 cell does contain a number and not text, the formula will return “Yes” into the output cell.
If cell contains specific text, then return a value
To find a cell that contains specific text, use the formula below.
- Select the output cell, and use the following formula: =IF(cell=”text”, value_to_return, “”).
- For our example, the cell we want to check is A2, the text we’re looking for is “example”, and the return value will be Yes. In this scenario, you’d change the formula to =IF(A2=”example”, “Yes”, “”).
- Because the A2 cell does consist of the text “example”, the formula will return “Yes” into the output cell.
4. If cell contains specific text, then return a value (case-sensitive)
To find a cell that contains specific text, use the formula below. This version is case-sensitive, meaning that only cells with an exact match will return the specified value.
- Select the output cell, and use the following formula: =IF(EXACT(cell,”case_sensitive_text”), “value_to_return”, “”).
- For our example, the cell we want to check is A2, the text we’re looking for is “EXAMPLE”, and the return value will be Yes. In this scenario, you’d change the formula to =IF(EXACT(A2,”EXAMPLE”), “Yes”, “”).
- Because the A2 cell does consist of the text “EXAMPLE” with the matching case, the formula will return “Yes” into the output cell.
5. If cell does not contain specific text, then return a value
The opposite version of the previous section. If you want to find cells that don’t contain a specific text, use this formula.
- Select the output cell, and use the following formula: =IF(cell=”text”, “”, “value_to_return”).
- For our example, the cell we want to check is A2, the text we’re looking for is “example”, and the return value will be No. In this scenario, you’d change the formula to =IF(A2=”example”, “”, “No”).
- Because the A2 cell does consist of the text “example”, the formula will return a blank cell. On the other hand, other cells return “No” into the output cell.
6. If cell contains one of many text strings, then return a value
This formula should be used if you’re looking to identify cells that contain at least one of many words you’re searching for.
- Select the output cell, and use the following formula: =IF(OR(ISNUMBER(SEARCH(“string1”, cell)), ISNUMBER(SEARCH(“string2”, cell))), value_to_return, “”).
- For our example, the cell we want to check is A2. We’re looking for either “tshirt” or “hoodie”, and the return value will be Valid. In this scenario, you’d change the formula to =IF(OR(ISNUMBER(SEARCH(“tshirt”,A2)),ISNUMBER(SEARCH(“hoodie”,A2))),”Valid “,””).
- Because the A2 cell does contain one of the text values we searched for, the formula will return “Valid” into the output cell.
To extend the formula to more search terms, simply modify it by adding more strings using ISNUMBER(SEARCH(“string”, cell)).
7. If cell contains several of many text strings, then return a value
This formula should be used if you’re looking to identify cells that contain several of the many words you’re searching for. For example, if you’re searching for two terms, the cell needs to contain both of them in order to be validated.
- Select the output cell, and use the following formula: =IF(AND(ISNUMBER(SEARCH(“string1”,cell)), ISNUMBER(SEARCH(“string2″,cell))), value_to_return,””).
- For our example, the cell we want to check is A2. We’re looking for “hoodie” and “black”, and the return value will be Valid. In this scenario, you’d change the formula to =IF(AND(ISNUMBER(SEARCH(“hoodie”,A2)),ISNUMBER(SEARCH(“black”,A2))),”Valid “,””).
- Because the A2 cell does contain both of the text values we searched for, the formula will return “Valid” to the output cell.
Final Thoughts
We hope you found this tutorial helpful in learning how to apply “if cell contains” formulas in Microsoft Excel. You can now see which cells include values, text, numbers, and other data. This allows you to conveniently explore, manage, and analyze your data. If you’re interested in expanding your Excel skills, you may also want to explore “Formulas in Excel” to enhance your ability to perform various calculations and manipulations within your spreadsheets.