How to Check Cells Contains a Specific Text In Excel

This guide will walk you through two different methods for using Excel functions to determine if a cell or range of cells contains a specific string of text. This is a useful skill for anyone working with large data sets in Excel, as it allows you to quickly and easily identify and filter out specific information.

Checking Cells using COUNTIF. In this example, we have a list of competitor groups in column A and their respective coaches’ names in column B. Let’s say we want to know if the captain is also in the same competing group. To do this, we will be using the COUNTIF function.

Here’s a step-by-step guide to using the COUNTIF function:

1. Select the cell you want to appear the result in.
2. In the cell, enter the formula =COUNTIF(
3. Then type in A2.
4. Since we know that the words that we are checking are separated by characters, we will be adding an asterisk with quotes (“*”) followed by an ampersand (&). The asterisk works as a wildcard character that will replace any character.
5. Then enter the cell B2, the word we are checking.
6. Then close with an ampersand (&) and an asterisk with quotes (“*”). The formula should look like this: =COUNTIF(A2,”*”&B2&”*”).
7. Press the Enter key on your keyboard. The result will return a 1 if the text is IN the group and 0 if the text IS NOT.
8. We can then change the result. Changing 1 to “Present” and 0 for “Absent”. We will add an IF function to the equation. =IF(COUNTIF(A2,”*”&B2&”*”),”Present”,” Absent”). This tells Excel if the condition is met, then it will return a “Present”, otherwise, an “Absent.”

Using the SEARCH Function. Using the same example, we will use the SEARCH function to check if the cell in B2 is in A2. The SEARCH function will return a number value that represents its position in the cell. Since the SEARCH function returns a number value, we will combine it with the ISNUMBER formula.

1. Select the cell you want to appear the result in.
2. In the cell, enter the formula =ISNUMBER(SEARCH(
3. Then type in B2 followed by a comma and type A2.
4. Then close with a parentheses and press Enter. This will return a TRUE or FALSE value if the captain is in the said competing group.
5. We can then change the result. Changing 1 to “Present” and 0 for “Absent”. We will add an IF function to the equation. =IF(ISNUMBER(SEARCH((A2,B2),”Present”,” Absent”). This tells Excel if the condition is met, then it will return a “Present”, otherwise, an “Absent.”

Conclusion

The COUNTIF function allows you to count the number of cells that meet a specific criterion, while the SEARCH function returns the position of the specific text within a cell or range of cells. Both of these functions can be used to efficiently analyze and organize data in Excel. By following the step-by-step guides provided in this tutorial, you will be able to use these techniques to quickly and easily identify and filter out specific information from your data.