Introduction to Excel Contain Functions
Excel contain functions are a group of functions used to search for specific text within a cell or range of cells. These functions are essential for data analysis and manipulation, allowing users to filter, validate, and extract data based on specific conditions. In this article, we will explore five essential Excel contain functions, their syntax, and examples of how to use them.1. COUNTIF Function
The COUNTIF function is used to count the number of cells in a range that meet a specific condition. The syntax for the COUNTIF function is:COUNTIF(range, criteria)
Where range is the range of cells to search, and criteria is the condition to apply.
For example, to count the number of cells in the range A1:A10 that contain the text “apple”, you would use the formula:
COUNTIF(A1:A10, “apple”)
The * wildcard is used to search for the text “apple” anywhere in the cell.
2. IF Function with CONTAINS
The IF function can be used in combination with the CONTAINS function to test if a cell contains specific text. The syntax for the IF function is:IF(logical_test, [value_if_true], [value_if_false])
Where logical_test is the condition to apply, value_if_true is the value to return if the condition is true, and value_if_false is the value to return if the condition is false.
For example, to test if the cell A1 contains the text “banana”, you would use the formula:
IF(ISNUMBER(SEARCH(“banana”,A1)), “Yes”, “No”)
This formula uses the SEARCH function to search for the text “banana” in the cell A1, and returns “Yes” if the text is found, and “No” if it is not.
3. SEARCH Function
The SEARCH function is used to search for specific text within a cell. The syntax for the SEARCH function is:SEARCH(find_text, within_text, [start_num])
Where find_text is the text to search for, within_text is the text to search in, and start_num is the position to start the search from.
For example, to search for the text “orange” in the cell A1, you would use the formula:
SEARCH(“orange”, A1)
This formula returns the position of the text “orange” in the cell A1, or a #VALUE! error if the text is not found.
4. FILTER Function
The FILTER function is used to filter a range of cells based on a specific condition. The syntax for the FILTER function is:FILTER(range, include)
Where range is the range of cells to filter, and include is the condition to apply.
For example, to filter the range A1:A10 to only include cells that contain the text “grape”, you would use the formula:
FILTER(A1:A10, ISNUMBER(SEARCH(“grape”, A1:A10)))
This formula uses the SEARCH function to search for the text “grape” in the range A1:A10, and returns only the cells that contain the text.
5. ISNUMBER and SEARCH Combination
The ISNUMBER and SEARCH functions can be used together to test if a cell contains specific text. The syntax for the ISNUMBER function is:ISNUMBER(value)
Where value is the value to test.
For example, to test if the cell A1 contains the text “pear”, you would use the formula:
ISNUMBER(SEARCH(“pear”, A1))
This formula uses the SEARCH function to search for the text “pear” in the cell A1, and returns TRUE if the text is found, and FALSE if it is not.
📝 Note: The SEARCH function is not case-sensitive, so it will match text regardless of the case. If you need to perform a case-sensitive search, you can use the FIND function instead.
| Function | Syntax | Example |
|---|---|---|
| COUNTIF | COUNTIF(range, criteria) | COUNTIF(A1:A10, "*apple*") |
| IF with CONTAINS | IF(logical_test, [value_if_true], [value_if_false]) | IF(ISNUMBER(SEARCH("banana",A1)), "Yes", "No") |
| SEARCH | SEARCH(find_text, within_text, [start_num]) | SEARCH("orange", A1) |
| FILTER | FILTER(range, include) | FILTER(A1:A10, ISNUMBER(SEARCH("grape", A1:A10))) |
| ISNUMBER and SEARCH | ISNUMBER(SEARCH(find_text, within_text)) | ISNUMBER(SEARCH("pear", A1)) |
In summary, the five Excel contain functions discussed in this article are essential for data analysis and manipulation. By using these functions, you can filter, validate, and extract data based on specific conditions, making it easier to work with large datasets. Whether you need to count the number of cells that contain specific text, test if a cell contains specific text, or filter a range of cells based on a condition, there is an Excel contain function that can help.
What is the difference between the SEARCH and FIND functions?
+The SEARCH function is not case-sensitive, while the FIND function is case-sensitive. This means that the SEARCH function will match text regardless of the case, while the FIND function will only match text that is in the same case.
How do I use the FILTER function to filter a range of cells?
+To use the FILTER function to filter a range of cells, you need to specify the range of cells to filter and the condition to apply. For example, to filter the range A1:A10 to only include cells that contain the text “grape”, you would use the formula: FILTER(A1:A10, ISNUMBER(SEARCH(“grape”, A1:A10))).
Can I use the COUNTIF function to count the number of cells that contain specific text in a case-sensitive manner?
+No, the COUNTIF function is not case-sensitive. However, you can use the COUNTIFS function with the FIND function to count the number of cells that contain specific text in a case-sensitive manner. For example, to count the number of cells in the range A1:A10 that contain the text “Apple” in a case-sensitive manner, you would use the formula: COUNTIFS(A1:A10, “Apple”).
