VBA to count periods in a single cell

[Bubba]

I am looking to count the number of periods within a single cell using vba.

For instance if I have 4 cells that contain the following:

5M09-0000700.01.10
5M09-0000700.01.10.10
5M09-0000700.01.10.10.10
5M09-0000700.01.10.10.20

Thus the output of the code for the first cell should be '2', the second
cell output would be '3', and the third/fourht cells would be '4' because
that many period characters were encountered. Data within these cells will
contain letters, numbers, periods, a few spaces and the "-" dash symbol. Any
help is greatly appreciated!

 

[Mike H]

Hi,

Try this

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

Mike

 

[Don Guillett]

One way

Option Explicit
Sub countperiodsincell()
Dim mc As Long
Dim i As Long
Dim j As Long
Dim ic As Double
mc = 9 ' column I
For i = 3 To 6 ' first row to last row in range
ic = 0
For j = 1 To Len(Cells(i, mc))
If Mid(Cells(i, mc), j, 1) = "." Then ic = ic + 1
Next j
MsgBox ic
Next i
End Sub

 

[Don Guillett]

vba

Sub countperiodsincellFormula()
Dim mc As Long
Dim i As Long
mc = 9 ' column I
For i = 3 To 6
MsgBox Len(Cells(i, mc)) - _
Len(Application.Substitute(Cells(i, mc), ".", ""))
Next i
End Sub

 

[Mike H]

Yes,

I missed that in the header of the post. Thanks.

Mike

 

[Ryan H]

Put this in a standard module.

Function CountPeriodsInCell(rw As Long) As Integer

CountPeriodsInCell = Len(Cells(rw, "I")) - _
Len(Application.Substitute(Cells(rw, "I"), ".", ""))

End Function

Then in any cell type "=CountPeriodsInCell(3)"
3- represents the row you want to calculate in Col. I

You will then see the number of periods in Range("I3")

Hope this helps! If so, let me know, click "YES" below.

 

[Rick Rothstein]

Give this a try...

Function CountDots(S As String) As Long
CountDots = UBound(Split(S, ".")) - (Len(S) = 0)
End Function