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