Excel VBA

css navigation by Css3Menu.com

Count Occurences in String

Trying to create a formula within Excel that will count the number of characters that appear within a cell. Ultimately I have a number of items in a cell that are separated by commas and I would like to know how many items there are. (ie..counting the number of commas should tell me this.)

My first reaction was, “it canít be done!,” and then I figured out how to do it.

Option Explicit

Public daSting As String, Z As Long, daRow As Long
Public stringLen, daAnsw, X

Sub CountCommas()
    Sheets("070996").Select
    daRow = Application.CountA(ActiveSheet.Range("A:A"))
    For Z = 1 To daRow      		'How many rows to work on
        daSting = Cells(Z, 1)   	'Get string
        stringLen = Len(daSting)    	'Length of String
        For X = 1 To stringLen      	'Increment thru
            Select Case Mid(daSting, X, 1)
                Case ","            	'If it is a comma
                    daAnsw = daAnsw + 1 'Add 1 to list
                Case Else       	'Do nothing
            End Select
        Next
    Cells(Z, 2) = daAnsw    		'Write the answer
    daAnsw = 0              		'Reset counter
    Next
End Sub

This has a somewhat elegant solution. There are 2 For…Next loops, the first to step through the rows of data and the second to step through each character in the string and determine if it is a comma (,).

Using this sequence, one could build a custom function that looks for the number of occurences of any character in the string.


© 2002-2017

Updated:  04/06/2017 20:32
This page added:  14 August 2002