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" wrote in message
...
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