Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to count periods in a single cell
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to count periods in a single cell
Hi,
Try this =LEN(A1)-LEN(SUBSTITUTE(A1,".","")) Mike "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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to count periods in a single cell
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike H" wrote in message ... Hi, Try this =LEN(A1)-LEN(SUBSTITUTE(A1,".","")) Mike "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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to count periods in a single cell
Yes,
I missed that in the header of the post. Thanks. Mike "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike H" wrote in message ... Hi, Try this =LEN(A1)-LEN(SUBSTITUTE(A1,".","")) Mike "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! . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to count periods in a single cell
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to count periods in a single cell
Give this a try...
Function CountDots(S As String) As Long CountDots = UBound(Split(S, ".")) - (Len(S) = 0) End Function -- Rick (MVP - Excel) "Bubba" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNT IF A VALUE FALLS WITHIN A RANGE IN A SINGLE CELL | Excel Discussion (Misc queries) | |||
How to count the number of months between 2 periods? | Excel Discussion (Misc queries) | |||
count of tab characters in a single cell | Excel Worksheet Functions | |||
Count periods in a cell | Excel Programming | |||
COUNT SEPARATE PERIODS IN COLUMN | Excel Programming |