Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 29

Thread: Solved: Character Count

  1. #1
    VBAX Regular Andrew's Avatar
    Joined
    Jun 2004
    Location
    Nagoya, Japan
    Posts
    13

    Solved: Character Count

    Hello the Forum,

    I have a little project that I am working on, which will require counting characters inside a cell.

    Let's say in cell A1, I have entered "VBA Express Forum" and I want to know how many times the character "e" occurs within the string, the answer should be 2. (Case does not matter, spaces and slashes should be included so " " or "/" will also be counted as well)

    As there does not seem an exisiting formula to do so already(?), I am wondering if it possible to make a Private Sub in VBA that will do the trick. I am thinking of something like =COUNTCH(A1,"e") to return 2.

    Any help you can give me would be appreciated. Thanks!

  2. #2
    VBAX Regular
    Joined
    Jun 2004
    Location
    The Netherlands
    Posts
    34
    Location
    Hello Andrew, to count the occurances of a char in a string you might think about using next formula:
    =LEN(A1) - LEN(SUBSTITUTE(Range, "YOURCHAR", "")) like
    =LEN(A1) - LEN(SUBSTITUTE(A1, "e", "")) where A1 contains the string and the char you wanted to look for is "e".

    if you want to use a User Defined function for this you might think about a function like:
    [VBA]
    Function COUNTCH(rng As Object, strText As String )
    Application.Volatile
    COUNTCH = Len(rng) - Len(Application.Substitute(rng, strText, ""))
    End Function
    [/VBA]
    Place this function into a module and called it like: =COUNTCH(A1,"e")

    Regards,
    Jeroen

  3. #3
    VBAX Regular
    Joined
    Jun 2004
    Location
    The Netherlands
    Posts
    34
    Location
    If you want to count the number of occurances within multiple cells you might think about using a function like:
    [VBA]
    Function CharCount(rng As Object, strText As String)
    Dim Counter As Integer
    Application.Volatile
    For Each rngVal In rng
    Counter = Counter + Len(rngVal) - Len(Application.Substitute(rngVal, strText, ""))
    Next rngVal
    CharCount = Counter / Len(strText)
    End Function
    [/VBA]

    to use this: =CharCount(A1:A10,"e")
    Last edited by roos01; 06-20-2004 at 10:25 AM.

  4. #4
    VBAX Regular
    Joined
    May 2004
    Location
    Seattle, WA
    Posts
    24
    Location
    Substitute is case sensitive. You need to change the formula given to:
    =LEN(A1) - LEN(SUBSTITUTE(LOWER(A1), LOWER("e"), ""))

    I for some reason keep getting an error when I try changing the given UDFs. I am short on time, so I will post this now.

  5. #5
    VBAX Regular
    Joined
    Jun 2004
    Location
    The Netherlands
    Posts
    34
    Location
    Like that thinking about using the case sensitive option. here another function which allows the user to count the chars within a range and detemine if he/she wants to count using casesensitive or not:
    [VBA]
    Function CharCount2(rng As Object, strText As String, Optional CaseSence As Boolean)
    Dim Counter As Integer
    Application.Volatile
    If CaseSence = True Then
    For Each rngVal In rng
    Counter = Counter + Len(rngVal) - Len(Application.Substitute(rngVal, strText, ""))
    Next rngVal
    Else
    For Each rngVal In rng
    Counter = Counter + Len(rngVal) - Len(Application.Substitute(LCase(rngVal), LCase(strText), ""))
    Next rngVal
    End If
    CharCount2 = Counter / Len(strText)
    End Function
    [/VBA]

    to use this:
    case sensitive: =CharCount2(A1:A10, "e", TRUE)
    non-casesensitive: =CharCount2(A1:A10, "e")

  6. #6
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    Good point Cesar,

    How about a slight modification?[vba]Function COUNTCH(rng As Object, strText As String, _
    Optional bCaseSen As Boolean = False) As Long
    Dim rngCell As Range, lCounter As Long

    lCounter = 0
    For Each rngCell In rng
    If bCaseSen = True Then
    lCounter = lCounter + Len(rngCell.Value) - _
    Len(Application.Substitute(rngCell.Value, strText, ""))
    Else
    lCounter = lCounter + Len(rngCell.Value) - _
    Len(Application.Substitute(LCase(rngCell.Value), LCase(strText), ""))
    End If
    Next rngCell
    COUNTCH = lCounter

    End Function[/vba]

    EDIT : I see Jeroen had the same idea
    EDIT2 : Extended to cover more than one cell

  7. #7
    VBAX Regular
    Joined
    Jun 2004
    Location
    The Netherlands
    Posts
    34
    Location
    LOL, same idea but now at least were complete. one function for a range and one function to be used for just one cell.

  8. #8
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    Nice stuff guys......

    BTW Jeroen, you don't need the
    Application.Volatile
    as the Function will update if any of the input parameters change

    Cheers

    Dave

  9. #9
    VBAX Regular
    Joined
    May 2004
    Location
    Seattle, WA
    Posts
    24
    Location
    The function needs to catch the case where the strText is not a single character. Either return an error or modified to handle strings of more than 1 character.

    If you call countch(A1,"his") with A1= "This is a test" it returns 3. Definitely not the right answer. Either 1 or #value should be returned.

    I am not sure what Andrew wanted. My opinion would be #value.

  10. #10
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    To generalise for any length string just factor in the length of the string. Amend the end of Richie's code like this ..

    [VBA] :
    :
    Next rngCell
    lCounter = lCounter / Len(strText)
    COUNTCH = lCounter

    End Function
    [/VBA]
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  11. #11
    MS Excel MVP VBAX Regular Colo's Avatar
    Joined
    May 2004
    Location
    Kobe, Japan
    Posts
    23
    Location

    Talking

    Hello Andrew,?@
    Hi guys, very nice approach, I add Extended to cover more than one RANGE with using ParamArray.
    Something like this..

    =COUNTCH("a",FALSE,B1:B3,C4:C7,E1:E10)

    As the HELP file says, ParamArray cannot be used with Optional keyword...so it depends on which one does Andrew want.

    Regarding to the problem that Cesar wrote, why don't you divide the result by the count of text?

    [vba]
    Option Explicit
    Sub Auto_Open()
    'Add discription to this UDF
    Application.MacroOptions Macro:="COUNTCH", _
    Description:="This is an UDF that counting characters inside a cell ", _
    HasShortcutKey:=False, _
    Category:=7
    End Sub
    Function COUNTCH(strText As String, _
    bCaseSen As Boolean, _
    rng As Range, _
    ParamArray OtherRng()) As Long
    Dim rngCell As Range, lCounter As Long, i As Long
    lCounter = 0
    For Each rngCell In rng
    lCounter = lCounter + CountUp(rngCell, lCounter, strText, bCaseSen)
    Next rngCell
    For i = 0 To UBound(OtherRng)
    For Each rngCell In OtherRng(i)
    lCounter = lCounter + CountUp(rngCell, lCounter, strText, bCaseSen)
    Next
    Next
    COUNTCH = lCounter
    End Function
    Private Function CountUp(r As Range, _
    lCounter As Long, _
    sTxt As String, _
    bCaseSen As Boolean) As Long
    If bCaseSen Then
    lCounter = (Len(r.Value) - _
    Len(Application.Substitute(r.Value, sTxt, ""))) / Len(sTxt)
    Else
    lCounter = (Len(r.Value) - _
    Len(Application.Substitute(LCase(r.Value), LCase(sTxt), ""))) / Len(sTxt)
    End If
    CountUp = lCounter
    End Function
    [/vba]

    HTH
    Last edited by Anne Troy; 06-21-2004 at 08:58 AM.

  12. #12
    VBAX Regular Andrew's Avatar
    Joined
    Jun 2004
    Location
    Nagoya, Japan
    Posts
    13

    Thanks Guys!

    Wow, talk about lots of help at hand! Thanks a lot, I really appreciate it!

    As a VBA newbie, it will take me a while to go through all the code, but I can see how the text functions work easily enough.

    Substitute the characters to be counted with spaces, then subtract the length (number) of the spaces from the length of the string. Then I see the suggestion of using lower case or (upper?) to ensure all characters are recognized by the SUBSTITUTE function.

    Perfect! This does exactly what I am after although I think I will work with an Imput Box later on to enter a string so I'll definitely be looking at the VBA code in order to make this possible.

    Without trying to embarass myself too much, could someone please explain why the Object variable is used and what Application.Volatile means.

    Thanks again, you guys are the greatest

  13. #13
    VBAX Regular
    Joined
    Jun 2004
    Location
    The Netherlands
    Posts
    34
    Location
    Hi Dave,
    I put in the example application.volatile so when ever the calculation is set to manual it still calculates the number of chars.

    Andrew:
    from VBA help:
    Object variables are stored as 32-bit (4-byte) addresses that refer to objects. Using the Set statement, a variable declared as an Object can have any object reference assigned to it.

    Volatile Method
    Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell

  14. #14
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    Here's a function for counting characters, using the character and range object. Makes the code a bit smaller. It will work with a single character on multiple ranges, ignoring case, doesn't care what the character is. Will return an error if you use more then 1 character in the selection

    syntax
    =CountChar(Range,"character")
    =CountChar(A1:A5,"e")
    [VBA]
    Function CountChar(R As Range, Char As String)
    Dim c As Characters, Found As Integer, count1 As Integer, R2 As Range
    If Len(Char) <> 1 Then
    CountChar = "#N/A"
    GoTo Error
    End If
    For Each R2 In R
    For count = 1 To R2.Characters.count
    If UCase(R2.Characters(count, 1).Text) = UCase(Char) Then
    Found = Found + 1
    End If
    Next count
    Next
    CountChar = Found
    Error:

    End Function
    [/VBA]

    edit:Thanks Dreamboat, It looks great this way.
    Last edited by CBrine; 06-22-2004 at 08:08 AM.
    The most difficult errors to resolve are the one's you know you didn't make.


  15. #15
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Hi, CBrine! Welcome!!
    Check out the link in my signature, and then edit your post to use VBA tags. They're VERY cool!
    ~Anne Troy

  16. #16
    VBAX Regular Andrew's Avatar
    Joined
    Jun 2004
    Location
    Nagoya, Japan
    Posts
    13

    More code for me to study!

    Hello again,

    I'm working my way down these posts one at a time absorbing wherever possible. Even if I don't understand everything it certainly helps me to go through the code and see what I can pick up.

    Just for the record, I was just thinking of counting single characters in a single cell (string).

    You've all been a tremendous help

  17. #17
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    My code will work with multiple or single range values. Doesn't matter.
    The most difficult errors to resolve are the one's you know you didn't make.


  18. #18
    VBAX Regular NateO's Avatar
    Joined
    Jun 2004
    Location
    Minneapolis, MN
    Posts
    90
    Location
    Another multi-range, [non vba] approach includes:

    =SUMPRODUCT(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,{"e","E"},"")))
    Regards,
    Nate Oliver

  19. #19
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Who's going to volunteer to make this a KB entry?

    I nominate Andrew.
    ~Anne Troy

  20. #20
    VBAX Regular Andrew's Avatar
    Joined
    Jun 2004
    Location
    Nagoya, Japan
    Posts
    13
    Dreamboat has nominated me to make this thread a KB entry.

    I'm okay with the non-VBA formulas but knowing which VBA I should use for the entry is causing smoke to come out from my ears.

    How about it guys, help a newbie out? (No kicking or scratching please )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •