##### Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

(80% Discount Ends Soon!)

# 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 ```