PDA

View Full Version : Solved: Count Occurrences of Letter w/in Cell?

skulakowski
09-16-2004, 10:27 AM
:help 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
09-16-2004, 10:38 AM
Hi, :006:

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
09-16-2004, 10:41 AM
I was thinking that I'd set up column for each character I was interested in.

Zack Barresse
09-16-2004, 11:20 AM
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
09-16-2004, 11:57 AM
Hi,

Maybe you could use a formula.

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

yogeshwarv
09-17-2004, 10:11 PM
What if we want to count total charactor of a cell???

yogeshwarv
09-17-2004, 10:15 PM
Ohh. Sorry I was forgot the LEN function??? I have got it..

parry
09-18-2004, 01:28 AM
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
09-18-2004, 08:47 AM
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
09-18-2004, 02:01 PM
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
09-23-2004, 02:04 PM
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.