Consulting

Results 1 to 11 of 11

Thread: Solved: Count Occurrences of Letter w/in Cell?

  1. #1

    Solved: Count Occurrences of Letter w/in Cell?

    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.

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    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?

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

  4. #4
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    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.

  5. #5
    MS Excel MVP VBAX Mentor Andy Pope's Avatar
    Joined
    May 2004
    Location
    Essex, England
    Posts
    341
    Location
    Hi,

    Maybe you could use a formula.

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

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

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

  8. #8
    VBAX Regular
    Joined
    Jul 2004
    Location
    Wellington, New Zealand
    Posts
    37
    Location
    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]

  9. #9
    VBAX Regular
    Joined
    Jun 2004
    Location
    The Netherlands
    Posts
    34
    Location
    or try this one:
    [VBA]
    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
    [/VBA]

    to use like:
    =HowMuch(A1,"w") or =HowMuch(A1:A10,"w")

  10. #10
    VBAX Regular
    Joined
    Jul 2004
    Location
    Wellington, New Zealand
    Posts
    37
    Location
    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.

  11. #11
    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
  •