# How to Find If Range of Cells Contains Specific Text in Excel

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.

### 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.
• 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.

## Similar Articles for You to Explore

<< Go Back to Find in String | String Manipulation | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Asikul Himel

Asikul Islam Himel, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, has contributed over two years to the ExcelDemy project. Starting as an Excel & VBA Content Developer, now he manages projects at You Have Got This Math Project. He wrote 60+ articles for ExcelDemy, reviewed 500+, and focused on quality maintenance. Currently, his responsibilities include project management and team leadership. Himel's interests encompass data analysis, leadership, WordPress applications, and... Read Full Bio

1. Hello Sir,

IN the above example, instead of Yes or NO, we need result as “Specific Text”. how to build the formula. pls help

Md. Abdur Rahim Rasel Aug 22, 2022 at 5:40 PM

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.

If the answer doesn’t fulfill your query, feel free to comment. Our Exceldemy Team is always there to help.

3. 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?”

Naimul Hasan Arif Nov 6, 2022 at 4:16 PM

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.

4. 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)

Ramesh Satyanarayanamurthy Jun 28, 2024 at 11:38 PM

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

Advanced Excel Exercises with Solutions PDF