Counting SubStrings and thier starting positions within Main Strin

[ExcelMonkey]

What is the easiest way to count the number of occurences of a substring
within a string AND the starting position of each substring? For Example:

String = "dog 123 cat 452 if 6754 dog"
SubString = "dog"

Occurences of SubString = 2
Starting Position of SubString = 1 and 26

Thanks

EM

 

[Toppers]

One way:

Public npos() as integer

Sub GetSubStrings(ByVal SearchString As String, FindStr As String)
n = 1
i = 0
Do
n = InStr(n, SearchString, FindStr)
If n <> 0 Then
ReDim Preserve Npos(i)
Npos(i) = n
n = n + 1
i = i + 1
End If
Loop Until n = 0
End Sub

Sub myTest()

Call GetSubStrings("dog 123 cat 452 if 6754 dog", "dog")

MsgBox "Number of strings = " & UBound(Npos) + 1
For i = 0 To UBound(Npos)
MsgBox Npos(i)
Next
End Sub

 

[Bob Phillips]

cTimes = (Len(sString) - Len(Replace(sString, sSubstring, ""))) _
/ Len(sSubstring)
sMsg = sSubstring & " occurs" & cTimes & " times" & vbNewLine
ipos = 0
For i = 1 To cTimes
ipos = InStr(ipos + 1, sString, sSubstring)
sMsg = sMsg & "#" & i & " occurs at: " & ipos & vbNewLine
Next i

MsgBox sMsg


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)