How to Find If A Range of Cells Contains Specific Text in Excel (4 Methods)

Using the COUNTIF Function to find cells contain specific texts

While handling a large database you may need to find a specific text from the database. Excel has some functions by which you can easily do that. Today in this article we will demonstrate several ways to find if the range of cells contains specific text in excel.

Download Practice Workbook

Download this practice sheet to practice while you are reading this article.

Find If A Range of  Cells Contains Specific Text in Excel (4 Methods)

1. Using the COUNTIF Function

To determine if a value or text exists in a range of data, you can simply use a formula based on the COUNTIF function.

Step-1:

Consider an example, where you have a range of different types of texts in the “Text” column. In another column named “Specific Text”, you are given certain definite texts which you have to find from the Text column and show your result in the “Result” column. In this section, we will do this task by using the COUNTIF formula.

 Using the COUNTIF Function to find cells contain specific texts

Step-2:

Now in cell E4, apply the COUNTIF function. The generic COUNTIF function is,

=COUNTIF(range,value)>0

Insert the values into the function and our final form of the function is,

=COUNTIF(B4:B18,”*”&D4&”*”)>0

Where,

  • Range is B4:B18
  • Criterion is “*”&D4&”*”. Here we used the Asterisk (*) as a Wildcard for one or more characters. We concatenated the asterisk before and after the cell reference D4 so now it will be counted as a substring. So this will count the value if it appears anywhere in the range.
  • If the value is found then the result is TRUE otherwise the result is FALSE.

 Using the COUNTIF Function to find cells contain specific texts

Press “Enter” to get the result.

 Using the COUNTIF Function to find cells contain specific texts

Step-3:

Now hover over your mouse cursor to the bottom right corner of the formula cell, and when the cursor shows the Fill handle icon (+), double click on it to apply the same formula for the rest of the cells.

 Using the COUNTIF Function to find cells contain specific texts

Step-4:

Now if we change some specific texts that do not exist in the Text column, then the formula will show the result as FALSE.

 Using the COUNTIF Function to find cells contain specific texts

2. Using the ISNUMBER Function

Using the formula based on the ISNUMBER and FIND, we can easily find the specific text from a range of cells.

Step-1:

We will use the same dataset from the previous example. In this case, the ISNUMBER function will be applied. Now in cell E4 apply the ISNUMBER with FIND. Insert the formula into the function and the final form is,

=ISNUMBER(FIND(D4,B4))

Where,

We have to find text D4 within text B4.

Using the ISNUMBER Function to find cells contain specific texts

Press “Enter” to get the result.

Using the ISNUMBER Function to find cells contain specific texts

Step-2:

 

Now, apply the same function to the rest of the cells.

Using the ISNUMBER Function to find cells contain specific texts

Step-3:

Now if we change some values, then the result will change from TRUE to FALSE.

Using the ISNUMBER Function to find cells contain specific texts

3. Using the IF Function to Find Cells Containing Specific Text

When we need to find specific texts from the given range of cells, we can easily do that with the IF function. Nesting other functions within the IF function will make our job easier.

              i. IF with the COUNTIF Function

Step-1:

In a cell where you want to get the result, apply IF with the COUNTIF formula.

The final form of this formula is,

=IF(COUNTIF(B4:B18,”*”&D4&”*”),”YES”,”NO”)

Where,

  • Range is B4:B18
  • Criterion is “*”&D4&”*”
  • If the value is found, the result will show “YES”
  • If the value is not found, the result will show “NO”

IF with COUNTIF function to find cells contain specific texts

Get the result by pressing “Enter”.

IF with COUNTIF function to find cells contain specific texts

Step-2:

Now apply the same to the rest of the specific texts. The Texts that matched with the source will show “YES” and others will show “NO”.

IF with COUNTIF function to find cells contain specific texts

             ii. IF with ISNUMBER

Step-1:

Apply the IF function with the ISNUMBER function in cell E4. The final formula is,

=IF(ISNUMBER(SEARCH(D4,B4)),”FOUND”,”NOT FOUND”)

Where,

  • We will find text D4 within text B4 using the SEARCH
  • The result will show “FOUND” if the value is true.
  • The result will show “NOT FOUND” if the value is false.

IF with ISNUMBER to find cells contain specific text

Press Enter to apply the function.

IF with ISNUMBER to find cells contain specific text

Step-2:

Now get the result for the rest of the cells.

IF with ISNUMBER to find cells contain specific text

Step-3:

Now if we change some values in the Specific Text column, the result will alter.

IF with ISNUMBER to find cells contain specific text

             iii. IF with OR

Step-1:

The formula based on IF with the OR function is also used to find a specific text from a range of cells. We now apply the IF with OR formula in cell E4. The final formula is,

=IF(OR(COUNTIF(B4,”*”&$D$4:$D$18&”*”)),”YES”,”NOT FOUND”)

Where,

  • Range is B4
  • Criterion is “*”&$D$4:$D$18&”*”
  • If the value is found, the result will show “YES”
  • If the value is not found, the result will show “NOT FOUND”

IF with OR to find cells contain specific text

Step-2:

Press Enter to get the result. Apply the same formula to the rest of the cells.

IF with OR to find cells contain specific text

4. Using the SUMPRODUCT Function

The SUMPRODUCT function also helps you to find specific texts in a range of cells. Follow these steps to learn.

Step-1:

Apply the SUMPRODUCT function in cell E4. Here we nested the COUNTIF function within the SUMPRODUCT function. The final formula is,

=SUMPRODUCT(COUNTIF(B4:B18,”*”&D4&”*”))>0

Where,

  • Range is B4:B18
  • Criterion is “*”&D4&”*”
  • The COUNTIF function counts the number of matched cells.
  • The SUMPRODUCT function takes the number returned by the COUNTIF function and gets its sum.

Using the SUMPRODUCT Function to find cells contain specific text

Now press Enter to get the result.

Using the SUMPRODUCT Function to find cells contain specific text

Step-3:

Apply the same formula to the rest of the cells. The result is achieved concerning the input.

Using the SUMPRODUCT Function to find cells contain specific text

Things to Remember

⏩ While we are using wildcards, we need to apply the asterisk ( * ) with each substring. Asterisk ( * ) matches any number of characters when used.

⏩ While using the IF with OR formula remember to Block the Range by using absolute cell reference ($)

Conclusion

Today we learned how to find specific texts within a range of cells using some formula-based methods. If you have any confusion or suggestion regarding the article you are most welcome to share your thoughts in the comment section.

Similar Articles for You to Explore

Tags:

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo