Jonayed
Sometimes we need to search if a range of cells contains specific text in Excel. In this article, we are going to show you how to search if a range of cells contains specific text in Excel with 3 easy approaches. We have also included some relevant concerns at the end of this article. So, let’s get started.
📂 Download Excel File
Download the free workbook from here:
Learn to Check If Cells Contain Any Specific Text with 3 Approaches in Excel
While writing this article, “Excel If Range of Cells Contains Specific Text ”, we’ve considered a dataset that contains approximately 5 columns and 15 rows. In this dataset, we added Quotes, Specific Text, and Output to make you better understand. But if you want, you can change the entities and put the values of your own.
1. Applying COUNTIF Function
Applying the COUNTIF to search if a range of cells contains specific text in Excel is a very simple process. You can do it easily by following the given steps.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell E6.
- Secondly, write the given formula on the cell.
=COUNTIF(B6:B15,”*”&D6&”*”)>0
🔨 Formula Breakdown
Here-
👉 B6:B15 is the range value.
👉 *&D6&* is the criteria where Asterisk (*) is used as Wildcard.
- Now press Enter.
- Thirdly, dragging Fill Handle , drag the formula to the remaining cells.
- Finally, you will get the result as well.
- If you want to change any Specific Text then you will get the output like the one below.
Truly, this process is very easy to search if any range of cells contains specific text.
2. Adopting ISNUMBER Function
ISNUMBER function in Excel is one of the easiest techniques to search if a range of cells contains specific text. You just have to maintain the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, select cell E6.
- Secondly, put the given formula down on the cell.
=ISNUMBER(FIND(D6,B6))
🔨 Formula Breakdown
Here-
👉 FIND(D6,B6) implies that cell D6 will find the value of corresponding cell B6.
👉 ISNUMBER(FIND(D6,B6)) means if the formula matches then it will give TRUE otherwise we will get FALSE.
- Tap Enter.
- Thirdly, using the Fill Handle icon, you just drag the formula to the remaining cells.
- Consequently, you will get your required result.
Undoubtedly this technique is very user-friendly to work on.
📕 Read More: 5 Ways to Return True If Value Exists in Column in Excel
3. Application of IF Function
You can also apply the IF function if you want to search if a range of cells contains specific text in Excel. You need to add some other functions with the IF function to obtain the result.
3.1 Using IF and ISNUMBER Functions
Using IF and ISNUMBER functions together is a good method to search if a range of cells contains specific text in Excel. You just need to follow the given steps.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select cell E6.
- Secondly, write the given formula down on that particular cell.
=IF(ISNUMBER(SEARCH(D6,B6)),”FOUND”,”NOT FOUND”)
🔨 Formula Breakdown
Here-
👉 ISNUMBER(SEARCH(D6,B6) means that the cell D6 will search for the corresponding values in B6.
👉 IF(ISNUMBER(SEARCH(D6,B6)),”FOUND”,”NOT FOUND”) implies that if the cell D6 finds the corresponding values in cell B6 then it will give FOUND, otherwise NOT FOUND.
- Now you need to press Enter.
- Thirdly, applying the Fill Handle icon, drag the formula to the remaining cells of the column.
- Eventually, you will see the required output.
Finally, this is a straightforward method to create list from range in Excel.
3.2 IF and COUNTIF Functions
Applying IF and COUNTIF functions together is an easy technique to search if a range of cells contains specific text in Excel.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, you need to select cell E6.
- Secondly, you have to write the following formula on the cell.
=IF(COUNTIF(B6:B15,”*”&D6&”*”),”YES”,”NO”)
🔨 Formula Breakdown
Here-
👉 B6:B15 is the range and
👉 &D6& is the criteria for the function.
👉 COUNTIF(B6:B15,”*”&D6&”*”),”YES”,”NO” implies that if the criteria matches with the values of the range then it will YES, otherwise it will give NO.
- Tap Enter.
- Now apply the Fill Handle and drag the formula to the remaining cells.
- Consequently, the desired result will appear to you.
Truly this is a very handy and easy procedure to search if a range of cells contains specific text in Excel.
3.3 Applying IF and OR Functions
If you want you can use IF and OR functions combined to search if a range of cells contains specific text in Excel. Follow the given steps and you will easily achieve this.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, you have to select cell E6.
- Secondly, write the given formula on that particular cell.
=IF(OR(COUNTIF(B6,”*”&$D$6:$D$15&”*”)),”YES”,”NOT MATCHED”)
🔨 Formula Breakdown
Here-
👉 &$D$6:$D$15& gives the criteria as values of the range.
👉 ”YES”,”NOT MATCHED” means if the value is found then YES otherwise NOT MATCHED.
👉 OR(COUNTIF(B6,”*”&$D$6:$D$15&”*”) implies that the criteria will determine the values of the corresponding cells whether it is TRUE or FALSE.
- You have to tap Enter.
- Thirdly, you have to use the Fill Handle icon to drag the formula to the remaining cells of the column.
- Finally, you will find the result.
Undoubtedly, this technique is very simple to search if a range of cells contains specific text in Excel.
📕 Read More: 5 Ways to Find First Occurrence of a Value in a Range in Excel
How to Check If Column Contains Partial Text in Excel
If you want to search for partial text in a column in Excel then you have come to right place. We are going to show you how to do this easily with some simple steps.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select cell C6.
- Secondly, put the following formula there.
=IF(COUNTIF(B6,”*Shakespeare*”),TRUE,FALSE)
🔨 Formula Breakdown
Here-
👉 COUNTIF(B6,”*Shakespeare*”) implies if the cells contains name Shakespeare and
👉 IF(COUNTIF(B6,”*Shakespeare*”),TRUE,FALSE) means if the cells contains name Shakespeare then its TRUE otherwise FALSE.
- Now tap Enter.
- Thirdly, use the Fill Handle icon to drag the formula to the remaining cells of the column.
- Eventually, you will get the result as well.
Lastly, this is a very simple procedure to search if a column contains partial text in Excel.
📕 Read More: 4 Quick Ways to Find Last Column with Data in Excel
📄 Important Notes
While performing the processes mentioned above, you need to be aware of these things:
🖊️ You need to be careful while using the above-mentioned Excel functions.
🖊️ In the given formula, you need to define the required cells carefully.
🖊️ If you have any confusion about those Excel functions, then you need to learn them first.
📝 Takeaways from This Article
If we summarize the whole article, we have got some points.
📌 Firstly, we’ve applied the COUNTIF function to search if a range of cells contains specific text in Excel.
📌 Secondly, we have used the ISNUMBER function.
📌 Thirdly, adopted the IF function with some other functions.
1. IF and ISNUMBER functions.
2. IF and COUNTIF functions.
3. IF and OR functions.
📌 Finally, we’ve tried to show you to search if the column contains partial text in Excel.
Conclusion
We wish that you would be able to search if a range of cells contains specific text in Excel using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If there is any skepticism in your mind, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.
