String occurance

04-11-2002, 12:29 PM
Will someone please remind me of the fucntion that counts the number of occurance of a substring in a string.


numberOfcommas = someFunction("there, are four, commas, in, this string", ",")

Thanks in advace

04-11-2002, 12:31 PM
InStr() in a loop.

04-11-2002, 12:43 PM
Or use the InstrCount function from VBSpeed. Here (

04-11-2002, 12:44 PM
instead of a loop using InStr()

you could use the split function and return the UBOUND of the resulting array for the # of occurances....

Dim s As String
Dim results() As String

s = "There, are, three, comma's in this string"

results = Split(s, ",")

MsgBox (UBound(results))

04-11-2002, 12:45 PM
I don't recall a VB function to count sub strings, but you can do this...

Dim strString As String
Dim aTemp() As String
Dim lCount As Long

strString = "there, are four, commas, in, this string"
aTemp = Split(strString, ",")
lCount = UBound(aTemp)

Msgbox "There are " & CStr(lCount) & " commas in that string."

Ales Zigon
04-11-2002, 12:46 PM
Or something like this:

Private Sub Command1_Click()
Dim sString$
sString = "there, are four, commas, in, this string"
MsgBox NumberOfOcc(sString, ",")
End Sub

Function NumberOfOcc(ByVal MyString As String, sDelimiter As String) As Integer
Dim aDel$()
aDel = Split(MyString, sDelimiter)
NumberOfOcc = UBound(aDel)
End Function

04-11-2002, 12:46 PM
Good idea. So long as he isn't doing a lot of these in a loop. Split() is real slow.

04-11-2002, 12:52 PM

you are right (split is slow) but probably not much slower than if you had to do a lot of looping.... speed vs. readability

04-11-2002, 01:08 PM
Don't forget all the wonderful functions in this thread, along with
their relative speeds.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum