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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

## Similar Articles for You to Explore Asikul Himel

Hi! I am Asikul Islam Himel. Glad you are here. I am a Team Leader of ExcelDemy, running an excellent team of five efficient Excel & VBA Content Developers. Here at ExcelDemy, we give the best sustainable solutions by posting articles related to MS Excel-related problems. I have completed my under graduation degree from Bangladesh University of Engineering and Technology and my program was Naval Architecture and Marine Engineering. I have found passion in data analysis and research-based fields. I am currently working to grow my leadership quality. I have a great interest in project management and critical thinking. In my free time, I love to travel and read books.

1. Reply Hello Sir,

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

2. Reply 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. Reply 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?”

• Reply 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. Reply 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?

• Reply 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) Advanced Excel Exercises with Solutions PDF  