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.
|