Check If Range of Cells Contains Specific Text

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

Share This Story, Choose Your Platform!

Leave A Comment

Excelx.com is a Excel Blog by PNRao to provide latest tips of Excel and help you to Quickly Learn and up-to date with Excel Concepts. We help you to know – how to do things in Excel with Clean and Easy Excel Tips, Shortcuts, Reference, Tutorials and New and Quick Tricks and Methods.

We Provide Free Online Tutorials, Formulas, Templates, Dashboards and Macros to excel in Microsoft Excel. We cover basic to advanced concepts and share creative ideas, Excel tips and Shortcuts with Related Example Files. You Can refer verity of examples covered on latest tools and techniques in Microsoft Excel Applications.

Related Posts