Counting characters in a range of cells

[DavidObeid]

Is there a way to count to number of times a given charachter occurs in
a range of cells?

 

[Bob Phillips]

David,

If it's a worksheet function you want, try

=SUM(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,"e","")))

which is an array formula, so enter with Ctrl-Shift-Enter.

If you want to do it in VBA, use

Debug.Print
Application.Evaluate("SUM(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,""e"","""")))")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[BrianB]

tRY THIS :-
'---------------------------------------------------------
Sub COUNT_CHAR()
Dim MyString As String
Dim Counter As Long
Dim MyChar As String
Dim MyLen As Integer
'---------------------------
Counter = 0
MyChar = InputBox("Enter a character or string (CASE SENSITIVE) ")
MyLen = Len(MyChar)
'- main loops
For Each c In Selection.Cells
MyString = c.Value
For n = 1 To Len(MyString)
If Mid(MyString, n, MyLen) = MyChar Then
Counter = Counter + 1
End If
Next
Next
MsgBox ("Character(s) " & MyChar & " appeared " & Counter &
times.")
End Sub
'-------------------------------------------------------

 

[DavidObeid]

Dear BrianB,

Thanks, but that doesn't seem to quite do what I was hoping for.

Firstly, I am a VBA newbie, so I may be making an idiot of myself in
saying what I am about to say.

I want to be able to read from a range of cells, not from a string a
user enters in a form.

Regards,

Dave

 

[J.E. McGimpsey]

Try:

Public Function CountChar(sChar As String, rRng As Range) As Long
Dim cell As Range
For Each cell In rRng
CountChar = CountChar + Len(cell.Text) - _
Len(Replace(cell.Text, sChar, ""))
Next cell
End Function

Call as

num = CountChar("a", Range("A1:J100"))