469,150 Members | 1,942 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,150 developers. It's quick & easy.

String occurence count

jimleon
74
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
Apr 30 '07 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. UBound(Split(StringToSearch,WordToCount))

17 10927
MMcCarthy
14,534 Expert Mod 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Function compareStrings(strSearch As String, strFull As String) As Integer
  2. Dim count As Integer
  3. Dim pos As Integer
  4. Dim newPos As Integer
  5. Dim endStr As Boolean
  6.  
  7.    endStr = False
  8.    pos = 1
  9.    Do until endStr
  10.       pos = newPos
  11.       If Not IsNull(InStr(pos, strFull, strSearch)) Then
  12.          count = count + 1
  13.          newPos = InStr(pos, strFull, strSearch)
  14.       Else
  15.          endStr = True
  16.       End If
  17.    Loop
  18.  
  19.    compareStrings = count
  20.  
  21. End Function
  22.  
Mary
Apr 30 '07 #2
jimleon
74
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.
Expand|Select|Wrap|Line Numbers
  1. Function compareStrings(strSearch As String, strFull As String) As Integer
  2. Dim count As Integer
  3. Dim pos As Integer
  4. Dim newPos As Integer
  5. Dim endStr As Boolean
  6.  
  7.    endStr = False
  8.    pos = 1
  9.    Do until endStr
  10.       pos = newPos
  11.       If Not IsNull(InStr(pos, strFull, strSearch)) Then
  12.          count = count + 1
  13.          newPos = InStr(pos, strFull, strSearch)
  14.       Else
  15.          endStr = True
  16.       End If
  17.    Loop
  18.  
  19.    compareStrings = count
  20.  
  21. End Function
  22.  
Mary
Apr 30 '07 #3
MMcCarthy
14,534 Expert Mod 8TB
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 ...

Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(InStr(pos, strFull, strSearch)) Then
Apr 30 '07 #4
MMcCarthy
14,534 Expert Mod 8TB
OK, this one is tested and should work.
Expand|Select|Wrap|Line Numbers
  1. Function compareStrings(strSearch As String, strFull As String) As Integer
  2. Dim count As Integer
  3. Dim pos As Integer
  4. Dim newPos As Integer
  5. Dim endStr As Boolean
  6.  
  7.    endStr = False
  8.    pos = 1
  9.    count = 0
  10.    Do Until endStr
  11.       newPos = Nz(InStr(pos, strFull, strSearch), 0)
  12.       If newPos <> 0 Then
  13.          count = count + 1
  14.          pos = newPos + 1
  15.       Else
  16.          endStr = True
  17.       End If
  18.    Loop
  19.  
  20.    compareStrings = count
  21.  
  22. End Function
  23.  
Mary
Apr 30 '07 #5
NeoPa
32,165 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. UBound(Split(StringToSearch,WordToCount))
May 2 '07 #6
MMcCarthy
14,534 Expert Mod 8TB
You could try using Split() in the following way :
Expand|Select|Wrap|Line Numbers
  1. UBound(Split(StringToSearch,WordToCount))
Interesting! I would never have thought of using UBound in that way.
May 2 '07 #7
ADezii
8,800 Expert 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function fSubString_Count(ByVal strString1 As String, ByVal strString2 As String) As Integer
  2.  
  3. Dim intCurrent_Pos As Integer, intNext_Pos As Integer
  4.  
  5. intNext_Pos = 0
  6. fSubString_Count = 0
  7.  
  8. intNext_Pos = InStr(1, strString1, strString2)
  9.  
  10. Do Until intNext_Pos = 0
  11.   fSubString_Count = fSubString_Count + 1
  12.   intCurrent_Pos = intNext_Pos + Len(strString2)
  13.   intNext_Pos = InStr(intCurrent_Pos, strString1, strString2)
  14. Loop
  15. End Function
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Debug.Print fSubString_Count("Cornwall - booked, devon - booked, wales booked", "booked") ==> 3
May 2 '07 #8
NeoPa
32,165 Expert Mod 16PB
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
May 2 '07 #9
ADezii
8,800 Expert 8TB
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.
May 2 '07 #10
hariharanmca
1,977 1GB
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub ChrOccurencesInString(pStrValue As String)
  2.     Dim chrArray() As String
  3.     Dim strTmp As String
  4.     Dim intArray() As Integer
  5.     Dim i As Integer, j As Integer, x As Integer, TotalCharacter As Integer
  6.     pStrValue = UCase(Trim(pStrValue))
  7.     ReDim chrArray(Len(pStrValue)) As String
  8.     ReDim intArray(Len(pStrValue)) As Integer
  9.     x = 0
  10.     strTmp = ""
  11.     TotalCharacter = 0
  12.     For i = 1 To Len(pStrValue)
  13.         If InStr(1, strTmp, Mid(pStrValue, i, 1)) = 0 Then
  14.             x = x + 1
  15.             chrArray(x) = Mid(pStrValue, i, 1)
  16.             strTmp = strTmp & chrArray(x)
  17.             intArray(x) = 0
  18.             intArray(x) = UBound(Split(pStrValue, Mid(pStrValue, i, 1)))
  19.         End If
  20.     Next i
  21.     strTmp = ""
  22.     For i = 1 To x
  23.         strTmp = strTmp & vbCr & chrArray(i) & " =>> " & intArray(i)
  24.         TotalCharacter = TotalCharacter + intArray(i)
  25.     Next i
  26.     strTmp = strTmp & vbCr & String(10, "=") & vbCr & " Tot = " & TotalCharacter & vbCr & String(10, "=")
  27.     MsgBox strTmp, vbInformation, "Character Count In String"
  28. End Sub
this code will gime multipule char occurences in a string
May 2 '07 #11
NeoPa
32,165 Expert Mod 16PB
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 :(
May 2 '07 #12
prn
254 Expert 100+
You could try using Split() in the following way :
Expand|Select|Wrap|Line Numbers
  1. 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
May 3 '07 #13
ADezii
8,800 Expert 8TB
: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.
May 3 '07 #14
NeoPa
32,165 Expert Mod 16PB
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).
May 3 '07 #15
NeoPa
32,165 Expert Mod 16PB
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.
May 3 '07 #16
rijon2
1
This one works..tested OK...

Expand|Select|Wrap|Line Numbers
  1. Function compareStrings(strSearch As String, strFull As String) As Integer
  2. Dim count As Integer
  3. Dim pos As Integer
  4. Dim newPos As Integer
  5. Dim endStr As Boolean
  6. newPos = 1
  7. count = 0
  8.    endStr = False
  9.    pos = 1
  10.    Do Until endStr
  11.       pos = newPos
  12.       If (InStr(pos, strFull, strSearch)) Then
  13.          count = count + 1
  14.          newPos = InStr(pos, strFull, strSearch) + 1
  15.       Else
  16.          endStr = True
  17.       End If
  18.    Loop
  19.  
  20.    compareStrings = count
  21.  
  22. End Function
Jul 16 '12 #17
NeoPa
32,165 Expert Mod 16PB
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.
Jul 17 '12 #18

Post your reply

Sign in to post your reply or Sign up for a free account.

By using this site, you agree to our Privacy Policy and Terms of Use.