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&”*”)>0Here, I have just mentioned the range

B5:B19. So, in this way, it is not mandatory to know the exact row whereWinter is Comingis 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, andLENfunctions. 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:B19and 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)