Excel Forum

Count the Number of Occurances of Any Variation of a Set of Characters within a Range of Cells in Excel

This Excel macro allows you to count how many times any variation of characters appears within a range of cells wtihin the spreadsheet.

For instance, you can use this to figure out how many cells contain the letters a, b, c, d within a range regardless of the order in which these characters appear within any cell within the range.  As long as a cell in the range contains these four characters, the cell will be included in the final count.

This macro can be adapted to work in many different ways; however, it currently loops through the cells in a single column.  By default, this works on column A.  If you want to change that, change the A in this line

For i = 1 To Range("A" & Rows.Count).End(xlUp).Row

to the letter of the column through which you want to loop.  And, change the number 1 in this line:

cellnums = Cells(i, 1).Value

to the number of the column through which you want to loop.  Column A is 1; column B is 2; and so on.

To change the characters for which you want to search, change the values for these for variables to whatever you want:

one = 1
two = 2
three = 3
four = 4

If you want to check for text, make sure to surround the text with double quotation marks.


Where to install the macro: Module


Count the Number of Occurances of Any Variation of a Set of Characters within a Range of Cells in Excel

Select All
Sub CountVariationOccurance()

'used to output the number of occurances found in the range
instanceCount = 0

'the four characters that you want to check for within a cell
'if you want to use text here, make sure to put them within double quotes
one = 1
two = 2
three = 3
four = 4

'loop through the range
For i = 1 To Range("A" & Rows.Count).End(xlUp).Row

    cellnums = Cells(i, 1).Value
   
    If InStr(1, cellnums, one) > 0 And InStr(1, cellnums, two) > 0 And InStr(1, cellnums, three) > 0 And InStr(1, cellnums, four) > 0 Then
        instanceCount = instanceCount + 1
    End If

Next i

'output the count
MsgBox instanceCount

End Sub