Excel: count instances of a string within a string 1

[megmoo75]

Does anyone know if there is a function in Excel to count the instances of one string within another string?

For example, if I have this value in my cell:

1.7.2.2

I want to be able to count the number of "." that occur in the cell. So the value for the example above would be 3.

Thanks in advance.

 

[Zathras]

It would be relatively easy to create a User-Defined Function (UDF) to do that, but if you want a non-VBA solution, the only one I can come up with uses several work cells to accomplish it.

Assuming the maximum number of dots is known, then this works (That's about all I can say for it.)

A1: 1.7.2.2
B1: =IF(ISERROR(FIND(".",$A$1)),0,FIND(".",$A$1))
C1: =IF(B1,IF(ISERROR(FIND(".",$A$1,B1+1)),0,FIND(".",$A$1,B1+1)),0)
D1: (copy from C1)
E1: (copy from C1)
etc. for as many dots as you may have
B2: =COUNTIF(B1:H1,">0")

 

[bluedragon2]

Here is a user defined functin that you can use. In a cell, put =cnt(".","A1") if what you are looking for the number of .'s in cell A1:

Public Function cnt(t As String, cl As String)

Dim tt As String
Dim i As Long

tt = Range(cl).Text
For i = 1 To Len(tt)
If Mid(tt, i, 1) = t Then
cnt = cnt + 1
End If
Next i

End Function



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Molby]

Here's an easier way.

=LEN(A1)-LEN(SUBSTITUTE(A1,".",""))

 

[bluedragon2]

Good one Molby, I thought I had did that before with the sub function, but couldn't remember. SFU...



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Molby]

Thanks Blue.