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

Please reply

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.

Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.