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.

excel if range of cells contains specific text


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.

Joining COUNTIF Functions to Find If Range of Cells Contains Specific Text in Excel

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.

Joining ISNUMBER and FIND Functions to Find If Range of Cells Contains Specific Text in Excel

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")

Joining IF with COUNTIF Functions to Find If Range of Cells Contains Specific Text in Excel

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")

Joining ISNUMBER, SEARCH, and IF Functions to Find If Range of Cells Contains Specific Text in Excel

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.

Join IF with OR and COUNTI Functions to Find If Range of Cells Contains Specific Text in Excel

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.

Join SUMPRODUCT and COUNTIF Functions to Find If Range of Cells Contains Specific Text in Excel

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


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

Get FREE Advanced Excel Exercises with Solutions!
Asikul Himel
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

6 Comments
  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

  2. Reply Avatar photo
    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.

    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.

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

    • Reply Avatar photo
      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))

      Solution

      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)

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo