Below is the Excel formula to Check If Range of Cells Contains Specific Text in Excel. You can check one or range of cells for specific text and return the values based on the occurrences for the further calculation. Here are the simple examples to search for a specific text and return True or False based on the existence of value in the given range.

Excel Formula to Check If Range of Cells Contains Specific Text

Here is the Excel formula to Check If Range of Cells Contains Specific Text. We have seen the previous example to check a cell for specific text. We can also check if range of cells contains specific text.


=IF(COUNTIF(A2:A12,"*Specific Text*"),"Yes","No")

Here, you can see that we are checking the range of cells A2:A12 for a specific text using IF and COUNTIF function. COUNIF function will count the cells with specific text and return the number. If the returning value is greater than zero (i.e; found the given text), IF function returns ‘Yes’ or else ‘No’. We can make this formula more dynamic by passing a cell reference.


=IF(COUNTIF(A2:A12,"*"&B1&"*"),"Yes","No")

Now, you can enter any specific text to check if the range of cells contains the given text.

Excel Formula to return Cells of a Range Contains Specific Text

The following function returns the Cells with specific text. We can use the COUNTIF function to return the count.


=COUNTIF(A2:A12,"*Specific Text*")

Excel If Range of Cells Contains Specific Text VBA

Here is the Excel VBA macro to check if Range of cells contains specific text using VBA.

'excel if range of cells contains specific text vba
Sub MacroToCheckIfRangeOfCellsContainsSpecificText_vba()

Set Rng = Range("A1:A12") ' You can change this
specificText = "Specific Text" ' You can change this


 For Each Cell In Rng.Cells
 
 If UCase(Cell.Value) Like "*" & UCase(specificText) & "*" Then
 Cell.Offset(0, 1) = "Yes! Found"
 Else
 Cell.Offset(0, 1) = "Not Found"
 End If
 Next
 
End Sub

Subscribe To Our Newsletter

Receive all of the latest news, templates and updates fresh from Excelx.com!

Published On: November 10th, 2020Last Updated: December 5th, 2022

About the Author: Valli

Hi! I'm Valli, a passionate VBA Developer. This blog is where I share that passion, helping you master Excel, VBA, and Power BI to analyze data, gain insights, and automate tasks. My goal is to equip you with the skills to become an expert and truly accelerate your work.
Check If Range of Cells Contains Specific Text

Share This Story, Choose Your Platform!

4 Comments

  1. Diyor November 17, 2022 at 7:07 pm - Reply

    Please fix the typo on the second formula!
    it is currently written as: =IF(COUNTIF(A2:A12,”*”&B1&*”),”Yes”,”No”)

    The above does not work, it should be written as: =IF(COUNTIF(A2:A12,”*”&B1&”*”),”Yes”,”No”)

    • PNRao December 5, 2022 at 11:22 am - Reply

      Thanks you, fixed!

  2. Ram G Kumar October 10, 2024 at 9:19 am - Reply

    Excel VBA macro to check if Range of cells contains a range of texts using VBA.

    • PNRao October 24, 2024 at 11:43 am - Reply

      Here is the VBA code to check Range of Cells contains the text of another range:

      Sub CheckIfRangeContainsTexts()
    Dim RangeToCheck As Range, SearchTextsRange As Range
    Dim Cell As Range, TextCell As Range
    Dim iFoundCount As Integer
    
    ' Set the ranges (adjust according to your needs)
    Set RangeToCheck = Sheet1.Range("A1:A10") ' Range to search in
    Set SearchTextsRange = Sheet1.Range("C1:C5") ' Texts to search for

    ' Loop through each cell in the RangeToCheck
    
    strFoundRanges = ""
    iFoundCount = 0
    For Each TextCell In SearchTextsRange
        ' Loop through each search text
        Found = False
        For Each Cell In RangeToCheck
            If InStr(1, TextCell.Value, Cell.Value, vbTextCompare) > 0 Then
                iFoundCount = iFoundCount + 1
                strFoundRanges = strFoundRanges & vbCr & TextCell.Address & ":" & TextCell.Value & " found in cell ---> " & Cell.Address
                Exit For
            End If
        Next Cell
    Next TextCell

    If iFoundCount = 0 Then
        MsgBox "None of the texts were found in the specified range."
    Else
        MsgBox iFoundCount & " texts were found:" & strFoundRanges
    End If
End Sub

      Hope this helps!
      Thanks
      PNRao

Leave A Comment