CODEWITHSUNDEEP
count
Solution Seeker
Solution Seeker

Reputation: 645

Count Occurrence of a word in a sentence in VBA

I want to count the occurrence of a particular word from a given string/sentence. I have already tried with below code but it's not working.

Sub main()
   MainStr = " yes no yes yes no yes no "
   Str1 = " yes "
   MsgBox UBound(Split(MainStr, Str1))
End Sub

In the above code, I want to search Str1 from the MainStr. In most of the blogs, People gave solution to use "Split" function to count the occurrence. But it won't give correct results when the search word comes immediately after each other.

In above scenario, the search word is " yes " & it's coming at the 3rd & 4th position.

The above code will give correct result for below scenario when, MainStr = " yes no yes no yes no yes " Str1 = " yes "

Please help me with this As I have already tried/searched a lot to find the solution.

Thanks !

Upvotes: 0

Views: 1265

Answers (2)

Mike Cheel
Mike Cheel

Reputation: 13106

You can try:

HowManyOccurrences = ubound(split(whole_string, search_string))

which splits the whole_string using the search_string as the delimiter into and array that returns the number of elements in the array.

You will have to loop through then most likely (or come up with a recursive regex and then count captures or something):

Dim whole_string As String
Dim search_string As String
Dim temp As String
Dim ctr As Integer

whole_string = "yes no yes yes no yes no "
search_string = "yes"
temp = whole_string
ctr = 0

While (InStr(1, temp, search_string) > 0)
    temp = Replace(temp, search_string, "", 1, 1)
    ctr = ctr + 1
Wend

MsgBox (ctr)

Upvotes: 3

Sorceri
Sorceri

Reputation: 8043

I beleive you will need a loop, Not sure you can accomplish this with just 1 line of code, maybe I am wrong


Dim s As String
Dim searchWord As String
Dim iSearchIndex As Integer
Dim iCounter
searchWord = "YES"
s = "yes no yes yes no no yes yes yes no yes"
Do While True
    'get the location of the search word
    iSearchIndex = InStr(1, UCase(s), "YES")
    'check to make sure it was found
    If iSearchIndex > 0 Then
        'create a string removing the found word
        s = Mid(s, iSearchIndex + Len(searchWord))
        'add 1 to our counter for a found word
        iCounter = iCounter + 1
    Else
        Exit Do 'nothing found so exit
    End If
Loop
MsgBox CStr(iCounter)

Upvotes: 0

Related Questions