excel vba count string in string code example

Example 1: excel vba word count in string

'Fast VBA function to count the words in a string. Words are delimited by
'white space. White space is ASCII 0 thru 32.

Function WordCount&(s$)
    Dim i&, b() As Byte
    If 0 = LenB(s) Then Exit Function
    b = s
    If b(0) > 32 Then WordCount = 1
    For i = 0 To LenB(s) - 4 Step 2
        If b(i) < 33 Then If b(i + 2) > 32 Then WordCount = WordCount + 1
    Next
End Function
        
'-----------------------------------------------------------------------------

MsgBox WordCount("A b cd" & vbcrlf & "eee")		'<--displays:  4

Example 2: excel vba count substring in string

'VBA functions to count the occurrnces of a substring in a string.

'Choose your function flavor:

'Small and Slow:
Function InStrCount&(s1$, s2$, Optional Compare As VbCompareMethod = vbBinaryCompare)
    If Len(s1) Then If Len(s2) Then InStrCount = UBound(Split(s1, s2, , Compare))
End Function
    
        
'Super Fast (3 times faster than the above function):    
Function InStrCount&(s1$, s2$, Optional ByVal Start& = 1, Optional Compare As VbCompareMethod = vbBinaryCompare)
    Dim s2L&
    If Compare = vbBinaryCompare Then
        s2L = LenB(s2)
        If s2L Then
            Start = InStrB(Start, s1, s2)
            Do While Start
                InStrCount = InStrCount + 1
                Start = InStrB(Start + s2L, s1, s2)
            Loop
        End If
    Else
        InStrCount = InStrCount(LCase$(s1), LCase$(s2), Start)
    End If
End Function   
    
'------------------------------------------------------------------------------
    
MsgBox InStrCount("oAooBoCooDo", "oo")		'<--displays:  2