String occurence count

[jimleon]

Hello,

I need a function to return the number of occurences of a defined string within another;
ie if the search string is 'booked' and the main string is 'Cornwall - booked, devon - booked, wales booked' then the function should return the numeric value of 3.
Is there a built-in function to achieve this?

Many thanks in advance

[MMcCarthy]

There is no built in feature that I know of to do this but the following should cover it. I haven't tested this so let me know if there are any errors.

Function compareStrings(strSearch As String, strFull As String) As Integer
Dim count As Integer
Dim pos As Integer
Dim newPos As Integer
Dim endStr As Boolean
 
   endStr = False
   pos = 1
   Do until endStr
      pos = newPos
      If Not IsNull(InStr(pos, strFull, strSearch)) Then
         count = count + 1
         newPos = InStr(pos, strFull, strSearch)
      Else
         endStr = True
      End If
   Loop
 
   compareStrings = count
 
End Function
 

Mary

[jimleon]

Hmmm. gives an invalid procedure error and the pos variable never goes above 1!

There is no built in feature that I know of to do this but the following should cover it. I haven't tested this so let me know if there are any errors.

Function compareStrings(strSearch As String, strFull As String) As Integer
Dim count As Integer
Dim pos As Integer
Dim newPos As Integer
Dim endStr As Boolean
 
   endStr = False
   pos = 1
   Do until endStr
      pos = newPos
      If Not IsNull(InStr(pos, strFull, strSearch)) Then
         count = count + 1
         newPos = InStr(pos, strFull, strSearch)
      Else
         endStr = True
      End If
   Loop
 
   compareStrings = count
 
End Function
 

Mary

[MMcCarthy]

Hmmm. gives an invalid procedure error and the pos variable never goes above 1!

Can you confirm this is the line the code is stopping at ...

If Not IsNull(InStr(pos, strFull, strSearch)) Then

[MMcCarthy]

OK, this one is tested and should work.

Function compareStrings(strSearch As String, strFull As String) As Integer
Dim count As Integer
Dim pos As Integer
Dim newPos As Integer
Dim endStr As Boolean
 
   endStr = False
   pos = 1
   count = 0
   Do Until endStr
      newPos = Nz(InStr(pos, strFull, strSearch), 0)
      If newPos <> 0 Then
         count = count + 1
         pos = newPos + 1
      Else
         endStr = True
      End If
   Loop
 
   compareStrings = count
 
End Function
 

Mary

[NeoPa]

Hello,

I need a function to return the number of occurences of a defined string within another;
ie if the search string is 'booked' and the main string is 'Cornwall - booked, devon - booked, wales booked' then the function should return the numeric value of 3.
Is there a built-in function to achieve this?

Many thanks in advance

You could try using Split() in the following way :

UBound(Split(StringToSearch,WordToCount))

[MMcCarthy]

You could try using Split() in the following way :

UBound(Split(StringToSearch,WordToCount))

Interesting! I would never have thought of using UBound in that way.

[ADezii]

Hello,

I need a function to return the number of occurences of a defined string within another;
ie if the search string is 'booked' and the main string is 'Cornwall - booked, devon - booked, wales booked' then the function should return the numeric value of 3.
Is there a built-in function to achieve this?

Many thanks in advance

There is no built-in Function to achieve this, but the following will work nicely. To find the number of occurences of one String within another (strString2 IN strString1), just pass both Arguments to the following Function:

Public Function fSubString_Count(ByVal strString1 As String, ByVal strString2 As String) As Integer
 
Dim intCurrent_Pos As Integer, intNext_Pos As Integer
 
intNext_Pos = 0
fSubString_Count = 0
 
intNext_Pos = InStr(1, strString1, strString2)
 
Do Until intNext_Pos = 0
  fSubString_Count = fSubString_Count + 1
  intCurrent_Pos = intNext_Pos + Len(strString2)
  intNext_Pos = InStr(intCurrent_Pos, strString1, strString2)
Loop
 End Function

OUTPUT:

Debug.Print fSubString_Count("Cornwall - booked, devon - booked, wales booked", "booked") ==> 3

[NeoPa]

There are many valid responses to the brilliance of post #6, ADezii, but saying it can't possibly exist is NOT one of them :D

[ADezii]

There are many valid responses to the brilliance of post #6, ADezii, but saying it can't possibly exist is NOT one of them :D

Sorry Neo, you lost me.

[hariharanmca]

Hello,

I need a function to return the number of occurences of a defined string within another;
ie if the search string is 'booked' and the main string is 'Cornwall - booked, devon - booked, wales booked' then the function should return the numeric value of 3.
Is there a built-in function to achieve this?

Many thanks in advance

You can try the below Code

Private Sub ChrOccurencesInString(pStrValue As String)
    Dim chrArray() As String
    Dim strTmp As String
    Dim intArray() As Integer
    Dim i As Integer, j As Integer, x As Integer, TotalCharacter As Integer
    pStrValue = UCase(Trim(pStrValue))
    ReDim chrArray(Len(pStrValue)) As String
    ReDim intArray(Len(pStrValue)) As Integer
    x = 0
    strTmp = ""
    TotalCharacter = 0
    For i = 1 To Len(pStrValue)
        If InStr(1, strTmp, Mid(pStrValue, i, 1)) = 0 Then
            x = x + 1
            chrArray(x) = Mid(pStrValue, i, 1)
            strTmp = strTmp & chrArray(x)
            intArray(x) = 0
            intArray(x) = UBound(Split(pStrValue, Mid(pStrValue, i, 1)))
        End If
    Next i
    strTmp = ""
    For i = 1 To x
        strTmp = strTmp & vbCr & chrArray(i) & " =>> " & intArray(i)
        TotalCharacter = TotalCharacter + intArray(i)
    Next i
    strTmp = strTmp & vbCr & String(10, "=") & vbCr & " Tot = " & TotalCharacter & vbCr & String(10, "=")
    MsgBox strTmp, vbInformation, "Character Count In String"
End Sub

this code will gime multipule char occurences in a string

[NeoPa]

Sorry Neo, you lost me.

:deflated balloon:
I was very impressed with myself for finding a solution with built-in function calls and relatively short to boot.
Then you tell everyone that it can't be done with built-in functions.
I nearly cried :(

[prn]

You could try using Split() in the following way :

UBound(Split(StringToSearch,WordToCount))

Very Perlish! :) I like it! (for what that's worth). It is brilliant, even if you do say so yourself. :)

And even though it isn't the sort of thing that leaps to mind immediately (until you've seen it), it's much easier to understand and much less prone to (e.g., typographical) errors than the alternatives that people keep posting. This one is going into my bag of tricks.

Thanks, NeoPa!

Paul

[ADezii]

:deflated balloon:
I was very impressed with myself for finding a solution with built-in function calls and relatively short to boot.
Then you tell everyone that it can't be done with built-in functions.
I nearly cried :(

Sorry Neo, for deflating your Balloon. What I meant was that there was no single Function that specifically returns what the OP requested. I commend you on your solution - it was quite elegant, intuitive, and resourceful. As we speck, I am looking for a long lost pump to re-inflate your Balloon. See ya soon.

[NeoPa]

Sorry Neo, for deflating your Balloon. What I meant was that there was no single Function that specifically returns what the OP requested. I commend you on your solution - it was quite elegant, intuitive, and resourceful. As we speck, I am looking for a long lost pump to re-inflate your Balloon. See ya soon.

I was just messin' wid ya ADezii :)
One thing I failed to mention earlier, is that it probably won't work from within SQL directly. Fine from VBA but not SQL I think (I doubt SQL handles arrays at all).

[NeoPa]

Very Perlish! :) I like it! (for what that's worth). It is brilliant, even if you do say so yourself. :)

And even though it isn't the sort of thing that leaps to mind immediately (until you've seen it), it's much easier to understand and much less prone to (e.g., typographical) errors than the alternatives that people keep posting. This one is going into my bag of tricks.

Thanks, NeoPa!

Paul

Thanks Paul.
I'm afraid I wasn't very modest was I ;)
I'm glad you appreciated the answer too though.

[rijon2]

This one works..tested OK...

Function compareStrings(strSearch As String, strFull As String) As Integer
Dim count As Integer
Dim pos As Integer
Dim newPos As Integer
Dim endStr As Boolean
newPos = 1
count = 0
   endStr = False
   pos = 1
   Do Until endStr
      pos = newPos
      If (InStr(pos, strFull, strSearch)) Then
         count = count + 1
         newPos = InStr(pos, strFull, strSearch) + 1
      Else
         endStr = True
      End If
   Loop
 
   compareStrings = count
 
End Function

[NeoPa]

It seems quite a lot of code for something that can be (already posted) handled in a single line of code.

It seems remarkably similar to post #2 also. The one difference I noticed would not typically be described as an improvement.