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 several ways to find if the range of cells contains specific text in excel.

**Table of Contents**hide

**Download Practice Workbook **

Download this practice sheet to practice while you are reading this article.

**Find If A Range of Cells Contains Specific Text in Excel (4 Methods) **

**1. ****Using the COUNTIF Function **

To determine if a value or text exists in a range of data, you can simply use a formula based on the **COUNTIF** function.

**Step-1:**

Consider an example, where you have a range of different types of texts in the **“Text”** column. In another column named **“Specific Text”**, you are given certain definite texts which you have to find from the Text column and show your result in the **“Result”** column. In this section, we will do this task by using the **COUNTIF** formula.

**Step-2:**

Now in cell **E4**, apply the **COUNTIF** function. The generic **COUNTIF** function is,

**=COUNTIF(range,value)>0**

Insert the values into the function and our final form of the function is,

**=COUNTIF(B4:B18,”*”&D4&”*”)>0**

Where,

- Range is
**B4:B18** - Criterion is
**“*”&D4&”*”**. 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. - If the value is found then the result is
**TRUE**otherwise the result is**FALSE**.

Press** “Enter”** to get the result.

**Step-3:**

Now 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.

**Step-4:**

Now if we change some specific texts that do not exist in the Text column, then the formula will show the result as** FALSE**.

**Read more:** **How to Find If Cell Contains Specific Text in Excel**

**2. ****Using the ISNUMBER Function**

Using the formula based on the **ISNUMBER** and **FIND**, we can easily find the specific text from a range of cells.

**Step-1:**

We will use the same dataset from the previous example. In this case, the** ISNUMBER** function will be applied. Now in cell** E4** apply the **ISNUMBER** with** FIND**. Insert the formula into the function and the final form is,

**=ISNUMBER(FIND(D4,B4))**

Where,

We have to find text** D4** within text **B4**.

Press **“Enter”** to get the result.

**Step-2:**

Now, apply the same function to the rest of the cells.

**Step-3:**

Now if we change some values, then the result will change from** TRUE** to **FALSE**.

**Read more:** **Excel Search for Text in Range**

**3. ****Using the IF Function to Find Cells Containing Specific 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.

#### ** i. IF with the COUNTIF Function**

**Step-1:**

In a cell where you want to get the result, apply **IF** with the **COUNTIF** formula.

The final form of this formula is,

**=IF(COUNTIF(B4:B18,”*”&D4&”*”),”YES”,”NO”)**

Where,

- Range is
**B4:B18** - Criterion is
**“*”&D4&”*”** - 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”**.

**Step-2: **

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”**.

** ii. IF with ISNUMBER**

**Step-1:**

Apply the **IF** function with the** ISNUMBER** function in cell **E4**. The final formula is,

**=IF(ISNUMBER(SEARCH(D4,B4)),”FOUND”,”NOT FOUND”)**

Where,

- We will find text
**D4**within text**B4**using the**SEARCH** - 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.

**Step-2:**

Now get the result for the rest of the cells.

**Step-3:**

Now if we change some values in the Specific Text column, the result will alter.

** iii. IF with OR**

**Step-1:**

The formula based on** IF** with the** OR** function is also used to find a specific text from a range of cells. We now apply the **IF** with **OR **formula in cell **E4**. The final formula is,

**=IF(OR(COUNTIF(B4,”*”&$D$4:$D$18&”*”)),”YES”,”NOT FOUND”)**

Where,

- Range is
**B4** - Criterion is
**“*”&$D$4:$D$18&”*”** - If the value is found, the result will show
**“YES”** - If the value is not found, the result will show
**“NOT FOUND”**

**Step-2:**

Press Enter to get the result. Apply the same formula to the rest of the cells.

**Read more:** **How to Find Text in Cell in Excel**

**4. ****Using the SUMPRODUCT Function**

The **SUMPRODUCT** function also helps you to find specific texts in a range of cells. Follow these steps to learn.

**Step-1:**

Apply the **SUMPRODUCT** function in cell **E4**. Here we nested the **COUNTIF** function within the **SUMPRODUCT** function. The final formula is,

**=SUMPRODUCT(COUNTIF(B4:B18,”*”&D4&”*”))>0**

Where,

- Range is
**B4:B18** - Criterion is
**“*”&D4&”*”** - The
**COUNTIF**function counts the number of matched cells. - The
**SUMPRODUCT**function takes the number returned by the**COUNTIF**function and gets its sum.

Now press **Enter **to get the result.

**Step-3:**

Apply the same formula to the rest of the cells. The result is achieved 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 texts within a range of cells using some formula-based methods. If you have any confusion or suggestion regarding the article you are most welcome to share your thoughts in the comment section.

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.