How to Apply “If Cell Contains” Formulas in Excel – 2024

How to Apply “If Cell Contains” Formulas in Excel

Excel’s “If Cell Contains” formulas are a powerful way to create conditional logic for managing data. These formulas allow you to check if a cell contains specific text or values, and based on that, perform actions like returning a result, highlighting cells, or setting flags. Whether you are working with text or numerical data, learning how to use the IF function in combination with logical operators like SEARCH or ISNUMBER can greatly enhance your data analysis capabilities.

In this guide, we will explain how to use “If Cell Contains” formulas, with step-by-step examples for checking whether a cell contains specific text or values.

Basic Structure of the IF Formula

The basic IF formula in Excel works like this:

• logical_test: The condition to test (e.g., if a cell contains a specific word).
• value_if_true: The result if the condition is TRUE.
• value_if_false: The result if the condition is FALSE.

Example 1: Check If a Cell Contains Specific Text

To check if a cell contains specific text, you can use the SEARCH function combined with IF. The SEARCH function returns the position of the text within the cell if found, and it will return an error if not found.

Formula Example:

• SEARCH(“apple”, A2) looks for the word “apple” in cell A2.
• ISNUMBER checks if the result is a number (i.e., if the word is found).
• If “apple” is found, the formula returns “Yes,” otherwise, it returns “No.”

Steps to Implement:

1. Select the Cell:
   • Click on the cell where you want to apply the formula (e.g., B2).
2. Enter the Formula:
   • Type the formula: =IF(ISNUMBER(SEARCH("apple", A2)), "Yes", "No").
3. Drag to Copy the Formula:
   • If you want to apply this to other rows, click the bottom-right corner of the cell and drag it down to copy the formula.

Result:

• If A2 contains the word “apple,” B2 will display “Yes”.
• If A2 does not contain “apple,” B2 will display “No”.

Example 2: Check for Partial Matches

If you want to check if a cell contains part of a text string, the SEARCH function can still be used.

Formula Example:

• This will check if the cell contains “Inc” (useful for finding companies that end with “Inc.”).

Steps:

1. Input the Formula:
   • In the target cell (e.g., B2), enter =IF(ISNUMBER(SEARCH("Inc", A2)), "Found", "Not Found").
2. Drag to Apply:
   • Drag down to apply the formula to other rows if needed.

Example 3: Check If a Cell Contains a Number

If you want to check whether a cell contains a number rather than text, you can use the ISNUMBER function directly.

Formula Example:

• ISNUMBER(A2) checks if the value in A2 is a number.
• The formula returns “Number” if true and “Not a Number” if false.

Steps:

1. Select the Cell:
   • Click the cell where you want the result (e.g., B2).
2. Enter the Formula:
   • Type the formula: =IF(ISNUMBER(A2), "Number", "Not a Number").
3. Copy to Other Cells:
   • Drag the formula down to apply to other cells as needed.

Example 4: Multiple Conditions Using IF and OR

You can use IF with the OR function to check for multiple text conditions.

Formula Example:

• This formula checks if A2 contains either “apple” or “banana”.
• If it contains either, it returns “Fruit”, otherwise “No Fruit”.

Steps:

1. Enter the Formula:
   • In the target cell, input =IF(OR(ISNUMBER(SEARCH("apple", A2)), ISNUMBER(SEARCH("banana", A2))), "Fruit", "No Fruit").
2. Copy the Formula:
   • Apply it to other cells as needed.

Example 5: Case-Sensitive “If Cell Contains”

By default, the SEARCH function is case-insensitive. If you need to check for text with case sensitivity, use the FIND function instead of SEARCH.

Formula Example:

• FIND(“Apple”, A2) checks for the exact case-sensitive match of “Apple”.
• If found, it returns “Yes”, otherwise “No”.

Conclusion

The “If Cell Contains” formula in Excel is a flexible and powerful tool that allows you to create custom logic based on your data. Whether you’re checking for specific words, numbers, or partial matches, using the combination of IF, SEARCH, ISNUMBER, and OR can simplify your data analysis and reporting tasks.

By mastering these formulas, you can significantly enhance your ability to handle data in Excel, enabling you to make more informed decisions and work more efficiently.