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!

0
Utf
12/22/2009 1:11:01 PM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
1410 Views

Similar Articles

[PageSpeed] 12

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!
> 
0
Utf
12/22/2009 1:18:01 PM
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@austin.rr.com
"Bubba" <Bubba@discussions.microsoft.com> wrote in message 
news:59257159-9DB4-495B-B420-C5563382D1F8@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!
> 

0
Don
12/22/2009 1:25:55 PM
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@austin.rr.com
"Mike H" <MikeH@discussions.microsoft.com> wrote in message 
news:0157D2F2-E5A3-4933-B5F3-DA6BE0780A26@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!
>> 

0
Don
12/22/2009 1:38:44 PM
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@austin.rr.com
> "Mike H" <MikeH@discussions.microsoft.com> wrote in message 
> news:0157D2F2-E5A3-4933-B5F3-DA6BE0780A26@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!
> >> 
> 
> .
> 
0
Utf
12/22/2009 3:00:01 PM
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!
> 
0
Utf
12/22/2009 3:08:02 PM
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@discussions.microsoft.com> wrote in message 
news:59257159-9DB4-495B-B420-C5563382D1F8@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!
> 

0
Rick
12/22/2009 8:06:27 PM
Reply:

Similar Artilces:

Duplicating a workbook only with cell's values
Hello every body, I have a complex workbook. It it is becaming a little bit difficul to work into it. It is possible to do a copy of this workbook but thi copy should have only cell�s values not cell�s formulas. As I said thi workbook is complex to replecate sheet by sheet to another workbook an do the paste especial process. thank you in advanc -- Message posted from http://www.ExcelForum.com Hi Adrix There is no option to save it like this Try this macro on a copy of your workbook Sub test() Worksheets.Select Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Worksheets(1).S...

Keeping the Same Cell References
What's the easiest way of copying a cell with a formula in it and pasting it into another cell with the exact same formula? I also have ASAP utlities, and I couldn't figure out how to do it with that either. One way: Remove the = sign before copying and re-insert it in the new location after pasting -- Kind regards, Niek Otten Microsoft MVP - Excel "JP" <JohnP26@msn.com> wrote in message news:qfian3pttvr0ju1eddj97quhiqon28snfl@4ax.com... | What's the easiest way of copying a cell with a formula in it and | pasting it into another cell with the exact same for...

Re: Find last cell in a column, Delete its contents and make it ac
Sub uselastcellinL() Application.Goto Cells(Rows.Count, "L").End(xlUp) ActiveCell.ClearContents End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Don Guillett" <dguillett1@gmail.com> wrote in message news:... > Sub deletelastcellinL() > Cells(Rows.Count, "L").End(xlUp).Delete shift:=xlUp > End Sub > > -- > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguillett@gmail.com > "George" <George@discussions.microsoft.com> wrote in message > news:D...

Cell Reference Formula Outside Workbook
I am trying to reference a cell outside of my current spread sheet. I am using a cell in my current sheet as an input for the extension of the file I would like to look in. So I have this defined as LINK. And the value of link is something like C:\\Myfolder\ready.xls I am trying to set up a formula in my sheet that will use this reference to the cell on the first page, which I defined as LINK so when I change the value of the cell all my values will update to the new folder and document, but retain their previous page, column and row references. For example. My current formula reads [...

Counting With Blank Rows
Hi, If I have a column of numbers like this: 4 5 6 3 7 8 9 3 2 7 4 1 4 How do I count the number of items between the blanks so it will look like I have it in the example. The number to the right and bottom is the count. So, for the numbers 4,5 6 the count is three. For 7,8 9 the count is three and for the numbers 2,7,4,1 the count is four. Is there a way to do this withoug using a macro? Thanks. Hi, You need to use the "count" function. If the rows in your example are 1-12 and the column is A, for the first group you'd type in (in column B) "=count(a1:a...

Sort report by sum of counts
I have a report that takes all issues from a certain date range and counts them. The report shows the sums only. I'm trying to sort by the sum in descending order, but it only sorts the detail, not the sum. Issues is the header, so it sorts first by alphabetically and then by the count for each day. I created another query that sums the orignal query data and created a report off the new query, but it still will only allow my to sort by the issue if issue is the header, instead of by sum. What am I missing? How can I sort the report by the overall sum of each issue for ...

Totals Won't Count Nulls
My Access Table has a columns containing various values, including null. See below. Error Code ------------ A A B C (Null) C D V (Null) I am trying to produce query results where each code is displayed with the number of times it appears by using Totals. Using design view, I drag the column name down twice, click Totals, then change one to "Group" and one to "Count". Fine except that my query results show zero for "Null" no matter how many nulls appear. Why won't it show 2 nulls, for example if the above data was being used? Here...

count cell with background color 36
First I want to count the number of cells in a range that have text in them. Then I want to count how many of these cells have the back ground color 36 Oldjay This will achieve what you want for a continuous range, Column A in this example. Change the variables to suit. Take care Marcus Option Explicit Sub MyCount() Dim lw As Integer Dim counter As Integer Dim MyConstant As Integer Dim i As Integer lw = Range("A" & Rows.Count).End(xlUp).Row counter = 0 MyConstant = Range("A2" & lw).SpecialCells(xlCellTypeConstants).Count MsgBox "The...

Ooh .. Linking a list to a list to an output cell
I have a single cell in which i enter the price of a home. Elsewhere, I have two columns of ten items each: one column is formulated to give relative prices based on the price entered in the single cell, and the column adjacent to this column describes these numbers in nominal terms (i.e. ten items in increments of five percent: "25% more" .. " 0% change" .. "25% less"). Further along, I have a list box created which uses the nominal column at the Source for the list. I would like to have a cell adjacent to the list box cell which will give the price b...

Named range(s) of non-adjacent cells return #VALUE! error in array formulas
I have ranges made up of non-adjacent cells from one worksheet and I get a #VALUE! error when I try to do any conditional count or sum calculation with an array formula on either or both of them. Each range is a selection of 32 non-adjacent cells from a single column. The cells contain array formulas that return percentages. Example of range values I8: 5.9% I11: 12.1% I14: 22.3% I17: 0.0% I was able to get values returned from simple functions like Max and Min but the following example gives me the #VALUE! error: {=SUM(IF((Rng>0),1,0))} =COUNTIF(Rng,"<.0125") Any wisdom ...

Whats the formula to look at data in precentages in fixed periods
how do I calculate a continous percentage return of two data points at fixed intervals? example : data set={12,50,03,59,22,....} , fixed interval=2periods. [(12-03)/12], [(50-59)/50], [(03-22)/03],....I'd like to do this in Excel 03 "donald" wrote: > how do I calculate a continous percentage return > of two data points at fixed intervals? > example : data set={12,50,03,59,22,....} , > fixed interval=2periods. > [(12-03)/12], [(50-59)/50], [(03-22)/03], Assuming data is in A2 down: 12,50,3,59,22,... In B2: =(A2-A4)/A2 B2 formatted as percentage, then copi...

Set up a single page to print a chronolgy of sheets printed
I am trying to set up a single page to print out a number (chronological order) each time that particular page is printed. Are you trying to increment a particular cell's value or to increment the page number, or to only print a number, and not the sheet? "srfr808" wrote: > I am trying to set up a single page to print out a number (chronological > order) each time that particular page is printed. ...

Can I separate data out of a single instance of CRM Online?
We have a group of separate organizations/companies that work together in a number of instances, and a "sales/management consultant," for lack of a better, non-proprietary way to describe the position, works with the sales teams at each of these organizations. He would like to pilot a CRM program with these organizations by using CRM Online. One easy way to set up this would be to have each organization/company represented by a business unit in CRM which the consultant can oversee. The concern, however, is whether or not the data for one of these business units/companies could be s...

VBA #3
I have a standard database which i need to upload it into my accounting software. In this database, one column is sale amount whereby sometime the value is NIL. Is anyone know how to write VBA to run down that particular column to delete those row with sale amount is NIL? Thank you so much in advance. Howard wrote: > I have a standard database which i need to upload it into my accounting > software. In this database, one column is sale amount whereby sometime the > value is NIL. > > Is anyone know how to write VBA to run down that particular column to delete > th...

Locking cells #7
Is it possible to lock the cells with conditio. I want to lock all the cells after one week from the certain date on the sheet. for Ex. $A$23 cell have the date 15 Aug 2005 and i want to lock the cell $C$8 to $H$23 after one week from that date. so that no one can change the data. Thanks in advance By default all cells are locked. You would need to unlock all desired and then have a macro to lock the desired cells. Sub lockcells() Range("b1:d12").Locked = False If Date > Range("a4") + 15 Then Range("b1:b4").Locked = True End Sub -- Don Guillett SalesA...

Running VBA Macro
Hello, It is possible to trigger a VBA macro that would run automatically every time the workbook "A" is open, if the date in Workbook "A" in row D15 is equal to the date in Workbook "B" E5? Sub Auto_Open() Dim wb As Workbook On Error Resume Next Set wb = Workbooks("Workbook B.xls") On Error GoTo 0 If wb Is Nothing Then MsgBox "Workbook B not open" Else If ThisWorkbook.ActiveSheet.Range("D15").Value = _ wb.ActiveSheet.Range("E5").Value Then 'run macro ...

Encrypt a Word document in VBA
I want to create a macro that in certain situations will encrypt the current document such that when the document is saved and opened again, Word will ask for the password (similarly to the user selecting the Office Button -> Prepare -> Encrypt Document). Would someone tell me how this can be done? Thanks for any help. To set a password ... ActiveDocument.Password = "whatever" To save with a password ... ActiveDocument.SaveAs name_etc., Password:="whatever" -- Enjoy, Tony www.WordArticles.com "JeffG" <JeffG@dis...

Please Help
i put vba textbox in word 2002, i exit design mode and saved BUT.. every time i open it up it opens up in design mode!!!!! what can i do to make the file open not in design mode??? i thought off writing in document_open : close the design mode, but i dont know the code line. all answers will do.. thenxs I bet you'd get better answers in an MSWord newsgroup (as opposed to an Excel newsgroup). DirectD@gmail.com wrote: > > i put vba textbox in word 2002, i exit design mode and saved BUT.. > every time i open it up it opens up in design mode!!!!! > > what can i do to mak...

How to start the default mail program using Excel VBA? #2
From Excel (using VBA) I want to click a button and start the default mail program installed on the computer. After this I want to prepopulate the TO line in Outlook Express or Outlook (depending on what's installed on the machine) with a few e-mail addresses (stored in my spreadsheet). Any sample code for this please? Thanks in advance Michalakis Michael michalakis_michael@hotmail.com ...

view & update (VBA-command button) two sheets concurrently
I would like to have two sheets visible concurrently so I can see the results of an update (both sheets) triggered by a command button (VBA macro code) on one of the sheets. Goto menu Windows> New Window and select the appropriate sheet, then Windows>Arrange... This wiull get your setup, do this with the macro recorder on and you get your code. --- HTH Bob Phillips "jay4690" <jay4690@discussions.microsoft.com> wrote in message news:FA58C497-08AA-466E-8304-BC1EB5C2CE04@microsoft.com... >I would like to have two sheets visible concurrently so I ...

stop rounding in cell
i want the numbers in my cells to show as they are without being rounded but to show in thousands. I want to maintain integrity. Eg. 97,654 should just show as 97. i don't want it to round up or down. is this possible? Hi Try =INT(A1/1000) but note anything less than 1000 will show as 0. Regards Roger Govier Lady112017 wrote: > i want the numbers in my cells to show as they are without being rounded but > to show in thousands. I want to maintain integrity. Eg. 97,654 should just > show as 97. i don't want it to round up or down. is this possible? Try this formula: ...

view cell content in header
Hi folks Can I customise the header so the left section = the content of a cell in the sheet eg A1. All I seem to be able to do is add date, page etc etc.. thanks john Images of home (NZ) http://www.titahi-bay.co.nz/home What we are up to in the UK http://www.titahi-bay.co.nz You need some VBA code to do this: You can use the workbook_beforeprint event to modify the header. Option Explicit Private Sub Workbook_BeforePrint(Cancel As Boolean) With Me.Worksheets("sheet1") .PageSetup.LeftHeader = .Range("a1").Text End With End Sub This goes behind the ThisW...

Click Entering Absolute Cell References into a Formula
When one is in the process entering a formula into an EXCEL spreadsheet it is possible to click on another cell to automatically have this cell reference placed into the formula. However this entry always is entered as a RELATIVE cell reference. Is there a way to have EXCEL insert an ABSOLUTE cell reference into the formula?? It seems to me that it would be so common and yet I have never been able to figure this one out. Please Help Michael Karas Hi Michael! Is this what you want? Click the cell and then press F4. F4 acts as a toggle going through the four reference options. -- ...

VBA Error
I have a Excel Spreadsheet which runs without problems on both Windows 2000, and Windows XP, but when I run the spreadsheet on my laptop with Windows 2000 I get the following error code. Microsoft Visual Basic Run-time error '9' Subscript out of range code stops hear: With Workbooks("Time_Sheet").Worksheets("Data_Entry").Range("A1").SpecialCells(xlCellTypeLastCell) End With With Range("A1").SpecialCells(xlCellTypeLastCell) Row = .Row Workbooks("Time_Sheet").Worksheets("Data_Entry").Names.Add Name:="Database", ...

Advanced Filter sort on "asterisk" (wild card) hides data, not just empty blank cells.
In debugging a program, I isolated the problem to the following issue. I manually created the following situation in column A, rows 1 to 6, by copying and pasting actual data in order to accurately re-create the problem: ABN/ACN/BN * ABN/ACN/BN 3158816 40000545415/005 Excel "Help" has this to say about the asterisk: " * (asterisk) Any number of characters in the same position as the asterisk For example, *east finds "Northeast" and "Southeast" " I am assuming that the asterisk, being the wild card symbol, being the "cell not empty" symbol, ...