The Excel CONTAINS function is a powerful tool that allows you to search for specific text within a cell and return a logical value (TRUE or FALSE) based on whether the text is found. This function is particularly useful when you need to perform conditional formatting, create dynamic data validation rules, or perform other actions based on the presence or absence of certain text in your spreadsheet. In this blog post, we will delve into the CONTAINS function, exploring its syntax, examples, and practical applications to help you master this valuable Excel feature.

Understanding the CONTAINS Function

The CONTAINS function in Excel is a built-in formula that checks if a specific substring is present within a given text string. It returns TRUE if the substring is found and FALSE otherwise. This function is particularly useful when you want to quickly identify cells containing specific words or phrases, making it an essential tool for data analysis and manipulation.

Syntax and Arguments

The syntax for the CONTAINS function is straightforward:

CONTAINS(text, substring)

• text: This is the main text or cell reference within which you want to search for the substring.
• substring: The substring you are looking for within the text.

Both arguments can be entered directly into the formula or referenced from cells containing the desired text.

Examples and Usage

Let's explore some practical examples to understand how the CONTAINS function can be utilized effectively:

1. Basic Usage

Suppose you have a list of employee names in Column A, and you want to identify rows where the name contains the word "Smith". You can use the CONTAINS function as follows:

=CONTAINS(A2, "Smith")

This formula will return TRUE for cells containing the word "Smith" and FALSE otherwise.

2. Conditional Formatting

The CONTAINS function is particularly useful for conditional formatting. For instance, if you want to highlight cells containing a specific word, you can create a conditional formatting rule using the CONTAINS function. Here's how:

1. Select the range of cells you want to format.
2. Go to the Home tab and click on Conditional Formatting.
3. Choose New Rule and select Use a formula to determine which cells to format.
4. In the formula bar, enter the CONTAINS function: =CONTAINS(A2, "Smith"), where A2 is the first cell in your range.
5. Click Format to choose the formatting options, such as font color or cell background.
6. Click OK to apply the conditional formatting rule.

Now, all cells containing the word "Smith" will be formatted as per your specified rules.

3. Data Validation

The CONTAINS function can also be used to create dynamic data validation rules. For example, if you want to restrict data entry in a cell to only values containing a specific substring, you can use the CONTAINS function in the data validation criteria.

1. Select the cell or range of cells you want to apply data validation to.
2. Go to the Data tab and click on Data Validation.
3. In the Data Validation dialog box, select Custom as the validation criteria.
4. In the formula bar, enter the CONTAINS function: =CONTAINS(A2, "Smith"), where A2 is the cell reference.
5. Click OK to apply the data validation rule.

Now, when you enter data into the specified cells, Excel will only allow values that contain the word "Smith".

4. Combining with Other Functions

The CONTAINS function can be combined with other Excel functions to perform more complex tasks. For instance, you can use the CONTAINS function along with the IF function to create a conditional statement that takes different actions based on whether the substring is found or not.

=IF(CONTAINS(A2, "Smith"), "Found", "Not Found")

This formula will return "Found" if the word "Smith" is found in cell A2, and "Not Found" otherwise.

Tips and Best Practices

• Case Sensitivity: By default, the CONTAINS function is case-insensitive, meaning it will return TRUE for "Smith" and "smith". If you need case-sensitive matching, you can use the EXACT function instead.
• Partial Matches: The CONTAINS function returns TRUE even if the substring is a part of a larger word. For example, "Smith" will match "Smithsonian".
• Wildcards: You can use wildcard characters such as "*" and "?" in the substring argument to perform more flexible searches. For example, "*" matches any sequence of characters, and "?" matches any single character.
• Multiple Substrings: If you need to search for multiple substrings, you can use the OR function along with the CONTAINS function. For example, =OR(CONTAINS(A2, "Smith"), CONTAINS(A2, "Johnson")) will return TRUE if either "Smith" or "Johnson" is found in cell A2.

Conclusion

The CONTAINS function is a versatile tool in Excel that allows you to quickly identify cells containing specific text. By understanding its syntax and applying it to various scenarios, you can enhance your data analysis and manipulation capabilities. Whether you're using it for conditional formatting, data validation, or complex formulas, the CONTAINS function is a valuable addition to your Excel toolkit.

FAQ