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

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

"ExcelMonkey" wrote:

> 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

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)

"ExcelMonkey" <(E-Mail Removed)> wrote in message
news:F7E88C35-1BC0-4B24-93F7-(E-Mail Removed)...
> 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