Instr just gives you the starting position of the first occurrence of "ABC".

Try this worksheet function
=(LEN($A2)-LEN(SUBSTITUTE($A2,B$1,"")))/LEN(B$1)

where your string is in A1 and "ABC" is in B1 (or you can hardcode "ABC" in
the formula. then copy down (and across if needed).

if it has to be done w/vba check vba help for the Replace function, which
works similar to the substitute function and construct a statement similar to
the one above.


"GettingThere" wrote:

I'm trying to count the number of times a sequence of characters occurs in a
cell. The sequence could be surrounded by non-printing characters, and/or
could appear more that once in one string (ABCblahABC SDF ABC blah).

I tried the following, but it seems to "miss" sometimes. Any reason why?

Thanks in advance!

Sub getNum()
Dim i As Long
Dim str As String

For i = 2 To Cells(Rows.Count, "a").End(xlUp).Row
str = InStr(Cells(i, 1), "ABC")
Cells(i, "B") = str
Next i

End Sub