
Solved: Count Occurrences of Letter w/in Cell?
Does Excel have a builtin function that will return the number of occurrences of a letter within a single cell  a kind of "countif" but for single cell.
Say cell A1 contains "XXZ". Can Excel tell me that there are 2 X's and 1 Z?
I've built the bruteforce equations to evaluate each single letter within the cell. I could define a function for myself. But I'm wondering if an easy, elegant function already exists to do this.
Thanks.

Hi,
So will you be determining the character to count in a seperate cell? Or do you just want all of them listed and their respective counts?

I was thinking that I'd set up column for each character I was interested in.

Well, you could do it with a User Defined Function (UDF) ...
[vba]Option Explicit
Function CountChars(rngOne As Range, rngTwo As Range) As Integer
Dim i As Integer, n As Integer
n = 0
For i = 1 To Len(rngOne)
If Mid(rngOne, i, 1) = rngTwo.Value Then
n = n + 1
End If
Next i
CountChars = n
End Function[/vba]
Sample file attached. It's bare bones, but first range is the range to check, the second range is the character to check for.

Hi,
Maybe you could use a formula.
A1: =XXY
A2: =LEN(A1)LEN(SUBSTITUTE(A1,"X",""))

What if we want to count total charactor of a cell???
Please reply

Ohh. Sorry I was forgot the LEN function??? I have got it..

Not sure if this is what your after...
[VBA]Public Function CountLetter(StrText As String)
Dim Arr(1 To 26) As Long, i As Long, CharNum As Long
If StrText = "" Then Exit Function 'Dont evaluate empty string
StrText = UCase(StrText) 'Turn string into upper case
For i = 1 To Len(StrText)
CharNum = Asc(Mid(StrText, i, 1)) 'Find ASCII # of character
If CharNum >= 65 And CharNum <= 90 Then 'If its a letter (ASC 65 to 90)
Arr(CharNum  64) = Arr(CharNum  64) + 1 'Add value in appropriate element
End If
Next i
CountLetter = Arr 'return array
End Function
Sub Example()
Dim i As Long, StrVal As String
StrVal = "Abefgfff"
'Populate A1:A26 with alphabet
For i = 1 To 26
Cells(i, 1) = Chr(64 + i)
Next i
'Place the number of occurances of each letter into B1:B26
Range("B1:B26") = Application.Transpose(CountLetter(StrVal))
End Sub[/VBA]

or try this one:
[VBA]
Function HowMuch(rng As Object, strText As String)
'http://www.excelcenter.com/mailing/016298h.htm
Dim rngAct As Range
Dim var As Variant
Dim intCounter As Integer
Application.Volatile
For Each rngAct In rng.Cells
var = rngAct.Value
intCounter = intCounter + Len(var)  Len(Application.Substitute(var, strText, ""))
Next rngAct
HowMuch = intCounter / Len(strText)
End Function
[/VBA]
to use like:
=HowMuch(A1,"w") or =HowMuch(A1:A10,"w")

Nice use of Len Roos1. The function I have placed above could also be used in a formula as it simply returns an array of 26 elements (1 for each letter) representing the count of each letter. By using the INDEX function you can return a particular element (ie letter) within the array. The index number will represent the position of the letter in the alphabet (eg B=2, J=10 etc).
So say your target cell is A1 and you want to count number of B's ...
=INDEX(COUNTLETTER(A1),2)
Count # J's
=INDEX(COUNTLETTER(A1),10)
Count # Z's
=INDEX(COUNTLETTER(A1),26)
You can also place number of each letter in cells automatically by selecting the number of cells for which you want to find letters then array entering the COUNTLETTER function.
eg
1. Select cells A1:Z1
2. Enter Formula =COUNTLETTER("Count number for each letter in this string")
3. Press CTRL+SHIFT+ENTER to confirm the formula
A1:Z1 will be populated with the count for each letter.
Example workbook attached.

Thank you all. I'm just coming back to my "count characters" problem after a few days meeting other deadlines.
Andy, your Len()  len(substitute()) equation is exactly the solution I couldn't imagine.
Right now, my counting problem is a simple, single column although I suspect...ha, I know it will grow, in which case I'll try the indexed function. As soon as I set up the Len()  len(substitute()) column, I know my group will want more!
Thanks again.
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules