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

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)