Prev: If cell value is greater than another cell value, clear contents.
Next: How to set the column title in a listbox from Excel
From: Ryan H on 22 Dec 2009 10:08
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.

--
Cheers,
Ryan


"Bubba" wrote:

> 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!
>
From: Rick Rothstein on 22 Dec 2009 15:06
Give this a try...

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

--
Rick (MVP - Excel)


"Bubba" <Bubba(a)discussions.microsoft.com> wrote in message
news:59257159-9DB4-495B-B420-C5563382D1F8(a)microsoft.com...
>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!
>

First  |  Prev  | 
Pages: 1 2
Prev: If cell value is greater than another cell value, clear contents.
Next: How to set the column title in a listbox from Excel