The column named Text contains the intended text and the column Specific Text contains the strings that will be checked on the left column. The Result column will show the outputs.
Method 1 – Insert the COUNTIF Function to Find If a Range of Cells Contains Specific Text in Excel
Steps:
- In cell E5, insert the formula:
=COUNTIF(B5:B10,"*"&D5&"*")>0
- Press Enter to get the result.
Formula Breakdown
- The input Range here is B5:B10.
- Criterion is “*”&D5&”*”. We used the Asterisk (*) as a Wildcard for one or more characters. We concatenated the asterisk before and after the cell reference D4 so it will be counted as a substring. This will count the value if it appears anywhere in the range.
- If the value is found, then the output will be TRUE. Otherwise, the output will be FALSE.
- Hover over to the bottom-right corner of the cell.
- When the cursor shows the Fill Handle icon (+), double-click on it to apply the same formula for the rest of the cells.
Read More: How to Find If Cell Contains Specific Text in Excel
Method 2 – Find If a Range of Cells Contains Specific Text with ISNUMBER and FIND Functions
Steps:
- In cell E5, apply the formula:
=ISNUMBER(FIND(D5,B5))
- 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.
- Apply the same function to the rest of the cells by dragging the Fill Handle to cell E10.
Read More: How to Find Text in Cell in Excel
Method 3 – Combine IF, OR, and COUNTIF Functions to Find Cells Containing Certain Text
Case 3.1 – IF with COUNTIF
Steps:
- In a cell where you want to get the result, use the following formula:
=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.
- AutoFill the column.
Case 3.2 – ISNUMBER, SEARCH, and IF Functions
Steps:
- In cell E5, use the following formula:
=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.
- Drag the Fill Handle to cell E10.
Case 3.3 – IF with OR and COUNTIF
Steps:
- In cell E5, input the formula:
=IF(OR(COUNTIF(B5,"*"&$D$5:$D$10&"*")),"YES","NOT FOUND")
- Press Enter to get the result.
Formula Breakdown
- The range is B5.
- Criterion is “*”&$D$5:$D$10&”*”.
- If the value is there, the result will show YES.
- 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.
Method 4 – Join SUMPRODUCT and COUNTIF Functions
Steps:
- Use the following formula in E5.
=SUMPRODUCT(COUNTIF(B5:B10,"*"&D5&"*"))>0
- 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.
- The SUMPRODUCT function takes the number returned by the COUNTIF function and gets its sum.
- Apply the same formula to the rest of the cells with AutoFill.
Download the Practice Workbook
Similar Articles for You to Explore
- How to Find from Right in Excel
- How to Find Character in String from Right in Excel
- Excel Find Last Occurrence of Character in String
- How to Find Character in Excel String
- How to Find * Character Not as Wildcard in Excel
<< Go Back to Find in String | String Manipulation | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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)
Hi,
I need help with searching multiple text in single cell using IF, ISNUMBER and SEARCH functions. Example – If there is a string “Service”, we need output as “Server Status”, If there is a string “Application”, we need output as “Application Status”, If there is a string “Connect”, we need output as “Connectivity”, If there is a string “CPU Utilization”, we need output as “CPU Utilization” etc.
Hello Ramesh Satyanarayanamurthy,
To search multiple text in single cell by using the IF,ISNUMBER and SEARCH function, use the following formula:
=IF(ISNUMBER(SEARCH(“Service”, B5)), “Server Status”, IF(ISNUMBER(SEARCH(“Application”, B5)), “Application Status”, IF(ISNUMBER(SEARCH(“Connect”, B5)), “Connectivity”, IF(ISNUMBER(SEARCH(“CPU Utilization”, B5)), “CPU Utilization”, “”))))
Output:
Regards
ExcelDemy