VBA to count periods in a single cell
in [Programming]
|
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: Bubba on 22 Dec 2009 08:11 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: Mike H on 22 Dec 2009 08:18 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! >
From: Don Guillett on 22 Dec 2009 08:25 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 Microsoft MVP Excel SalesAid Software dguillett1(a)austin.rr.com "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! >
From: Don Guillett on 22 Dec 2009 08:38 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 dguillett1(a)austin.rr.com "Mike H" <MikeH(a)discussions.microsoft.com> wrote in message news:0157D2F2-E5A3-4933-B5F3-DA6BE0780A26(a)microsoft.com... > 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! >>
From: Mike H on 22 Dec 2009 10:00
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 > dguillett1(a)austin.rr.com > "Mike H" <MikeH(a)discussions.microsoft.com> wrote in message > news:0157D2F2-E5A3-4933-B5F3-DA6BE0780A26(a)microsoft.com... > > 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! > >> > > . > |