Introduction to Excel IF Contains Formulas
Excel IF contains formulas are used to check if a cell contains a specific text or string. The IF function is a powerful tool in Excel that allows users to make logical comparisons between a value and what they expect. When combined with the SEARCH or ISNUMBER and SEARCH functions, the IF function can be used to check if a cell contains specific text. In this article, we will explore five different ways to use Excel IF contains formulas to achieve various tasks.Formula 1: Basic IF Contains Formula
The basic IF contains formula uses the SEARCH function to look for a specific text within a cell. The syntax of this formula is:=IF(ISNUMBER(SEARCH("text",A1)),"Text found","Text not found")
In this formula, “text” is the text you are looking for, and A1 is the cell you want to search in. The SEARCH function returns the position of the text if it is found, and the ISNUMBER function checks if the result is a number. If the text is found, the formula returns “Text found”, otherwise it returns “Text not found”.
Formula 2: Case-Insensitive IF Contains Formula
The basic IF contains formula is case-sensitive, meaning it treats “text” and “Text” as different strings. To make the formula case-insensitive, you can use the LOWER function to convert both the text and the cell contents to lowercase. The syntax of this formula is:=IF(ISNUMBER(SEARCH(LOWER("text"),LOWER(A1))),"Text found","Text not found")
This formula converts both the text and the cell contents to lowercase before searching, making the search case-insensitive.
Formula 3: IF Contains Formula with Multiple Criteria
You can also use the IF contains formula to check for multiple criteria. For example, you can check if a cell contains both “text1” and “text2”. The syntax of this formula is:=IF(AND(ISNUMBER(SEARCH("text1",A1)),ISNUMBER(SEARCH("text2",A1))),"Both texts found","Both texts not found")
This formula uses the AND function to check if both conditions are true. If both texts are found, the formula returns “Both texts found”, otherwise it returns “Both texts not found”.
Formula 4: IF Contains Formula with Wildcards
You can also use wildcards with the IF contains formula to search for patterns. For example, you can search for cells that contain “text” followed by any characters. The syntax of this formula is:=IF(ISNUMBER(SEARCH("text*",A1)),"Text found","Text not found")
This formula uses the * wildcard to match any characters after “text”.
Formula 5: IF Contains Formula with Multiple Cells
Finally, you can use the IF contains formula to check if any of the cells in a range contain a specific text. The syntax of this formula is:=IF(SUMPRODUCT(--ISNUMBER(SEARCH("text",A1:A10)))>0,"Text found","Text not found")
This formula uses the SUMPRODUCT function to count the number of cells that contain the text. If any of the cells contain the text, the formula returns “Text found”, otherwise it returns “Text not found”.
📝 Note: These formulas can be used in a variety of scenarios, such as data validation, filtering, and reporting.
In summary, Excel IF contains formulas are powerful tools that can be used to check if a cell contains a specific text or string. By using the SEARCH function and combining it with other functions, you can create complex formulas that can achieve various tasks. Whether you need to check for a single text, multiple texts, or patterns, there is an IF contains formula that can help you achieve your goal.
The following table summarizes the five IF contains formulas discussed in this article:
| Formula | Description |
|---|---|
| =IF(ISNUMBER(SEARCH(“text”,A1)),“Text found”,“Text not found”) | Basic IF contains formula |
| =IF(ISNUMBER(SEARCH(LOWER(“text”),LOWER(A1))),“Text found”,“Text not found”) | Case-insensitive IF contains formula |
| =IF(AND(ISNUMBER(SEARCH(“text1”,A1)),ISNUMBER(SEARCH(“text2”,A1))),“Both texts found”,“Both texts not found”) | IF contains formula with multiple criteria |
| =IF(ISNUMBER(SEARCH(“text*”,A1)),“Text found”,“Text not found”) | IF contains formula with wildcards |
| =IF(SUMPRODUCT(–ISNUMBER(SEARCH(“text”,A1:A10)))>0,“Text found”,“Text not found”) | IF contains formula with multiple cells |
In the end, mastering Excel IF contains formulas can help you become more efficient and effective in your work, and can help you to make better decisions with your data.
What is the purpose of the IF contains formula in Excel?
+
The IF contains formula is used to check if a cell contains a specific text or string, and to return a value based on that condition.
How do I make the IF contains formula case-insensitive?
+
To make the IF contains formula case-insensitive, you can use the LOWER function to convert both the text and the cell contents to lowercase.
Can I use the IF contains formula to check for multiple criteria?
+
Yes, you can use the IF contains formula to check for multiple criteria by using the AND function to combine multiple conditions.