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

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
>

Hi,

The loop you are using is only checking the first occurance of the string
and returning the position in thestring not the count. The following appears
to do the job.

Sub getNum()

Const csFind As String = "ABC"
Dim lRow As Long
Dim sStr As String
Dim iStrCount As Integer, iStartPos As Integer, iPosInStr As Integer

For lRow = 2 To Cells(Rows.Count, "a").End(xlUp).Row
sStr = Cells(lRow, 1)
iStrCount = 0
iPosInStr = 0
Do
iPosInStr = InStr(iPosInStr + 1, sStr, csFind)
If iPosInStr <> 0 Then iStrCount = iStrCount + 1
Loop Until iPosInStr = 0
Cells(lRow, 2) = iStrCount
Next lRow

End Sub

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"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
>