VBA - count substrings in string

[iknowu99]

Good day all, here is the goal:

string="ABBBBACCC"
goal=2 'number of occurances of "A"


Instead of going letter by letter (for looping), I am looking for a function to do this. THankS

 

[pgc01]

Hi iknowu99

Try:

MsgBox Len(string) - Len(Replace(string, "A", ""))

Hope this helps
PGC

 

[iknowu99]

clever trick! thanx

 

[JoeBB]

I know this is extremely long after the original post, but I want to share with everyone a very fast way to do this since it has had a good impact in my programs' speed.

Sub TestSpeedOfSubStringOccurences()
Dim T As Double, S As String, X As Long, L As Long, LngPos As Long
S = "ABBBBACCC"
Const SubString As String = "A"
T = Timer
For L = 0 To 10000000
    X = Len(S) - Len(Replace$(S, SubString, vbNullString, 1, -1, vbBinaryCompare))
Next L
MsgBox "Replace Function: " & Timer - T
T = Timer
For L = 0 To 10000000
    LngPos = 1
    X = 0
    Do

        LngPos = InStrB(LngPos, S, SubString, vbBinaryCompare)
        If LngPos > 0 Then
            X = X + 1
            LngPos = LngPos + 1
        End If
       
    Loop Until LngPos = 0
Next L
MsgBox "InStrB Function: " & Timer - T
End Sub

 

[Miceal]

Here is another way:

Dim S as String
S = "ABBBBACCC"
MsgBox "count = " & UBound(Split(S, "A"))

 

[eroock]

Here is another way:

Dim S as String
S = "ABBBBACCC"
MsgBox "count = " & UBound(Split(S, "A"))

Smart solution