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 4 easy 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.
4 Easy Ways to Find If Range of Cells Contains Specific Text in Excel
The dataset below will determine whether the range of texts contains some specific text or not. In addition, the column on the left named Text contains the intended text and the column on the right Specific Text contains the texts that will be checked on the left column. Then, the Result column will show the outputs. For avoiding any compatibility issues, use the Excel 365 edition.
1. Insert COUNTIF Function to Find If Range of Cells Contains Specific Text in Excel
To determine if a value or text exists in a range of data, you can simply use a formula based on the COUNTIF function. The COUNTIF function will help us to count values if some specific condition is met.
Steps:
- Firstly, in cell E5, insert the formula:
=COUNTIF(B5:B10,"*"&D5&"*")>0
- Then, press Enter to get the result.
🔎 Formula Breakdown
- The input Range here is B5:B10.
- Criterion is “*”&D5&”*”. 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.
- Therefore, if the value is found, then the output will be TRUE otherwise the output will be FALSE.
- After that 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.
- Hence, you’ll get the results.
Read more: How to Find If Cell Contains Specific Text in Excel
2. Find If Range of Cells Contains Specific Text with ISNUMBER and FIND Functions
Using the formula based on the ISNUMBER and FIND, we can easily find the specific text from a range of cells. The ISNUMBER function returns logical output if the argument inside of it satisfies. On the other hand, the FIND function returns the specific position of a specified text in a range of strings or text.
Steps:
- Firstly, in cell E5, we apply the formula:
=ISNUMBER(FIND(D5,B5))
- Then, press Enter to get the result.
🔎 Formula Breakdown
- The Find function here is determining the exact location of the text mentioned in the cell D5 in the text string B5. They can be a numerical value or can be a void (if the text is not found in the string).
- The ISNUMBER function will return logical output based on the output by the FIND function.
- After that, apply the same function to the rest of the cells by dragging the Fill Handle to cell E10.
- Hence you will get the desired result.
Read more: Excel Search for Text in Range
3. Combine IF, OR and COUNTIF Functions to Find Cells Containing Certain 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. Therefore, follow the below methods.
3.1 IF with COUNTIF Function
The combination of the IF and the COUNTIF functions will determine whether the intended string contains the specific text or not.
Steps:
- In a cell where you want to get the result, apply IF with the COUNTIF The final form of this formula is:
=IF(COUNTIF(B5:B10,"*"&D5&"*"), "YES","NO")
🔎 Formula Breakdown
- The range is B5:B10.
- Criterion is “*”&D5&”*”.
- 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.
- 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.
3.2 ISNUMBER, SEARCH, and IF Functions
We can determine whether the string contains the specific text or not with the combination of the IF, SEARCH, and ISNUMBER functions.
Steps:
- Firstly, apply the IF function with the ISNUMBER function in cell E5. The final formula is:
=IF(ISNUMBER(SEARCH(D5,B5)),"FOUND","NOT FOUND")
🔎 Formula Breakdown
- We will find text D5 within text B5 using the SEARCH function.
- 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.
- Hence you will get the result for the rest of the cells by dragging the Fill Handle to cell E10.
3.3 IF with OR and COUNTIF
Here, the combined application of IF, OR, and COUNTIF functions will be deployed to determine whether there is any text containing cells have any specific text or not. Where the IF function will help us to check out a condition, on the other hand, the COUNTIF will help us to count values if that specific condition is met. And the OR function will help us to return the required output based on the condition.
Steps:
- In cell E5, we input the formula:
=IF(OR(COUNTIF(B5,"*"&$D$5:$D$10&"*")),"YES","NOT FOUND")
- Hence, press Enter to get the result.
🔎 Formula Breakdown
- The range is B5.
- Criterion is “*”&$D$5:$D$10&”*”.
- Hence if the value is there, the result will show YES.
- Moreover, if the value is not found, the result will show NOT FOUND.
- Apply the same formula to the rest of the cells by dragging the Fill Handle icon to cell E10.
Read more: How to Find Text in Cell in Excel
4. Join SUMPRODUCT and COUNTIF Functions
The SUMPRODUCT and COUNTIF functions also help you to find specific texts in a range of cells. Follow these steps to learn. The SUMPRODUCT on the other hand the COUNTIF will help us to count values if that specific condition is met.
Steps:
- At first, apply the SUMPRODUCT function in cell E5. Here we nested the COUNTIF function within the SUMPRODUCT The final formula is:
=SUMPRODUCT(COUNTIF(B5:B10,"*"&D5&"*"))>0
- Hence press Enter to get the result.
🔎 Formula Breakdown
- The range is B5:B10.
- Criterion is “*”&D5&”*”.
- The COUNTIF function counts the number of matched cells.
- In addition, the SUMPRODUCT function takes the number returned by the COUNTIF function and gets its sum.
- Lastly, apply the same formula to the rest of the cells. The result is accurate 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 text if a range of cells contains it in Excel using some formula-based methods. Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.
Hello Sir,
IN the above example, instead of Yes or NO, we need result as “Specific Text”. how to build the formula. pls help
Hello RAVI
Thank you for your comment. When you are identifying the specific text from a string using Excel formulas, the specific text is known by you. So, I think if the return of the Excel formulas is TRUE you will be able to identify that specific text otherwise it returns FALSE. That’s why I think the above formula is appropriate to find out the specific text from a string. According to your question, I will introduce you to an efficient Excel function named the IF function to get the specific text. The IF function is,
=IF(E4=”YES”,D4,”Unable to Find Result”)Look at the below screenshot.
For the convenience of your work, please download the below Excel file which is provided by Exceldemy:
https://www.exceldemy.com/wp-content/uploads/2022/08/Excel-If-Range-of-Cells-Contains-Specific-Text.xlsx
If the answer doesn’t fulfill your query, feel free to comment. Our Exceldemy Team is always there to help.
Hello there! This is great, but I have a question. If we don’t know the row in which the sentence “Winter is coming” will appear. How could we search each row in Column B to find “Winter?”
Thanks for your comment. Look, I have used the following formula to Find If A Range of Cells Contains Specific Text in Excel.
=COUNTIF(B5:B19,”*”&D5&”*”)>0
Here, I have just mentioned the range B5:B19. So, in this way, it is not mandatory to know the exact row where Winter is Coming is written.
Hello,
IN the example, instead of Yes or NO, if we need result as numbers, like how many cells included the word Winter in the text, how to build the formula?
Dear RAZAN,
Thank you very much for reading our articles. Here, you mentioned that you do not want to get the answer in the form of Yes or No. You want to get the result in numbers. To solve your problem we formed a new formula based on the combination of the SUMPRODUCT, SUBSTITUTE, and LEN functions. Here is the formula:
=SUMPRODUCT((LEN($B$5:$B$19)-LEN(SUBSTITUTE($B$5:$B$19,$D5,"")))/LEN($D5))This formula will find out the specific word from the Range B5:B19 and return the sum in number.
If you want to get the number of a specific word from each cell you can follow this article.
Excel Formula to Count Specific Words in a Cell (3 Examples)