Introduction to Excel Contains Functions
Excel contains functions are used to search for a specific text or string within another text string. These functions are case-sensitive and can be used to extract, replace, or count the occurrences of a specific text. In this article, we will explore the different Excel contains functions, their syntax, and examples of how to use them.1. IF and SEARCH Functions
The IF and SEARCH functions can be used together to create a contains function. The SEARCH function returns the position of the first character of the text being searched for, and the IF function checks if the result is greater than 0.The syntax for this function is: =IF(ISNUMBER(SEARCH(“text”,A1)), “True”, “False”)
Where “text” is the text being searched for, and A1 is the cell containing the text to be searched.2. CONTAINS Function in Excel 2019 and Later
In Excel 2019 and later, the CONTAINS function can be used to search for a text string within another text string.The syntax for this function is: =CONTAINS(A1, “text”)
Where A1 is the cell containing the text to be searched, and “text” is the text being searched for.3. FILTER Function in Excel 2019 and Later
The FILTER function can also be used to search for a text string within another text string.The syntax for this function is: =FILTER(A:A, ISNUMBER(SEARCH(“text”, A:A)))
Where A:A is the range of cells containing the text to be searched, and “text” is the text being searched for.4. Using the FIND Function
The FIND function can be used to search for a text string within another text string. This function is case-sensitive.The syntax for this function is: =FIND(“text”, A1)
Where “text” is the text being searched for, and A1 is the cell containing the text to be searched.5. Using the COUNTIF Function with Wildcards
The COUNTIF function can be used with wildcards to search for a text string within another text string.The syntax for this function is: =COUNTIF(A:A, “text”)
Where A:A is the range of cells containing the text to be searched, and “text” is the text being searched for.📝 Note: The above functions can be used to search for a specific text or string within another text string, and can be modified to suit specific needs.
In the following table, we can see the comparison between the different Excel contains functions:
| Function | Syntax | Description |
|---|---|---|
| IF and SEARCH | =IF(ISNUMBER(SEARCH(“text”,A1)), “True”, “False”) | Searches for a text string within another text string |
| CONTAINS | =CONTAINS(A1, “text”) | Searches for a text string within another text string (Excel 2019 and later) |
| FILTER | =FILTER(A:A, ISNUMBER(SEARCH(“text”, A:A))) | Searches for a text string within another text string (Excel 2019 and later) |
| FIND | =FIND(“text”, A1) | Searches for a text string within another text string (case-sensitive) |
| COUNTIF with Wildcards | =COUNTIF(A:A, “text”) | Searches for a text string within another text string using wildcards |
In summary, the different Excel contains functions can be used to search for a specific text or string within another text string. Each function has its own syntax and usage, and can be modified to suit specific needs. By using these functions, users can easily search for and extract specific text from large datasets.
To further illustrate the usage of these functions, here are some examples: * Using the IF and SEARCH functions to check if a cell contains a specific text: =IF(ISNUMBER(SEARCH(“example”,A1)), “True”, “False”) * Using the CONTAINS function to search for a text string within another text string: =CONTAINS(A1, “example”) * Using the FILTER function to search for a text string within another text string: =FILTER(A:A, ISNUMBER(SEARCH(“example”, A:A))) * Using the FIND function to search for a text string within another text string: =FIND(“example”, A1) * Using the COUNTIF function with wildcards to search for a text string within another text string: =COUNTIF(A:A, “example”)
By mastering these Excel contains functions, users can become more efficient and effective in their work, and can easily extract and analyze specific data from large datasets.
In the end, the key to becoming proficient in using Excel contains functions is to practice and experiment with different formulas and techniques. By doing so, users can develop a deeper understanding of how to use these functions to achieve their goals and become more productive in their work.
What is the difference between the IF and SEARCH functions and the CONTAINS function?
+
The IF and SEARCH functions can be used together to create a contains function, while the CONTAINS function is a built-in function in Excel 2019 and later that can be used to search for a text string within another text string.
How do I use the FILTER function to search for a text string within another text string?
+
The FILTER function can be used to search for a text string within another text string by using the syntax: =FILTER(A:A, ISNUMBER(SEARCH(“text”, A:A)))
What is the difference between the FIND function and the SEARCH function?
+
The FIND function is case-sensitive, while the SEARCH function is not case-sensitive.
