Here's an easy way to calculate the number of occurrences of a string within another string.
| A | B | C | D | |
| 1 | String | Search String | Count | Formula |
| 2 | AaAaAaA | a | 3 | =(LEN(A2)-LEN(SUBSTITUTE(A2,B2,"")))/LEN(B2) |
| 3 | AaAaAaA | A | 4 | =(LEN(A3)-LEN(SUBSTITUTE(A3,B3,"")))/LEN(B3) |
The formula works by calculating the difference in string length before and after deleting the 'search string' from the original string. You could also use the same formula in VBA to accomplish the same thing.
Dim strString as string, strSearchString as string, lngCount as as long strString = "AaAaA" strSearchString = "a" lngCount = Len(strString)-Len(Substitute(strString,strSearchString,""))
Note
What do you think this formula will return?
=(LEN("aaaaa")-LEN(SUBSTITUTE("aaaaa","aa","")))/LEN("aa")
Hint: It's not anything like determining the number of triangles in triangles.