Asikul Himel
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.
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,
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.
Press “Enter” to get the result.
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.
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.
Read more: How to Find If Cell Contains Specific Text in Excel
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,
Where,
We have to find text D4 within text B4.
Press “Enter” to get the result.
Step-2:
Now, apply the same function to the rest of the cells.
Step-3:
Now if we change some values, then the result will change from TRUE to FALSE.
Read more: Excel Search for Text in Range
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,
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”
Get the result by pressing “Enter”.
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”.
            ii. IF with ISNUMBER
Step-1:
Apply the IF function with the ISNUMBER function in cell E4. The final formula is,
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.
Press Enter to apply the function.
Step-2:
Now get the result for the rest of the cells.
Step-3:
Now if we change some values in the Specific Text column, the result will alter.
            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,
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”
Step-2:
Press Enter to get the result. Apply the same formula to the rest of the cells.
Read more: How to Find Text in Cell in Excel
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,
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.
Now press Enter to get the result.
Step-3:
Apply the same formula to the rest of the cells. The result is achieved concerning the input.
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.
