Count Occurrences of Letter w/in Cell?

[skulakowski]

Does Excel have a built-in 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 brute-force 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.

[Zack Barresse]

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?

[skulakowski]

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

[Zack Barresse]

Well, you could do it with a User Defined Function (UDF) ...

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

Sample file attached. It's bare bones, but first range is the range to check, the second range is the character to check for.

[Andy Pope]

Hi,

Maybe you could use a formula.

A1: =XXY
A2: =LEN(A1)-LEN(SUBSTITUTE(A1,"X",""))

[yogeshwarv]

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

[yogeshwarv]

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

[parry]

Not sure if this is what your after...

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

[roos01]

or try this one:

Function HowMuch(rng As Object, strText As String)
'http://www.excel-center.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

to use like:

=HowMuch(A1,"w") or =HowMuch(A1:A10,"w")

[parry]

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.

[skulakowski]

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.