Results 1 to 7 of 7

Count occurrences of character in cell

This is a discussion on Count occurrences of character in cell within the Excel Questions forums, part of the Question Forums category; I would like to count the occurrences of a character in a cell. Example: A1 contains "name; name; name" In ...

  1. #1
    Board Regular
    Join Date
    Feb 2012
    Posts
    55

    Default Count occurrences of character in cell

    I would like to count the occurrences of a character in a cell.

    Example:

    A1 contains "name; name; name"

    In B1 I want to count the occurences of ";" and add with 1 to get the number of persons listed in A.

  2. #2
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    61,477

    Default Re: Count occurrences of character in cell

    Try

    =1+LEN(A1)-LEN(SUBSTITUTE(A1,";",""))
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular adam087's Avatar
    Join Date
    Jun 2010
    Location
    So'ton, UK
    Posts
    664

    Default Re: Count occurrences of character in cell

    Perhaps this Function

    Code:
    Option Explicit
     
    Public Function CountChar(InputRange As Range, CharToCount As String) As Long
     
        Dim i As Long, myCount As Long
        myCount = 0
     
        For i = 1 To Len(InputRange.Value)
            If Mid(InputRange.Value, i, 1) = CharToCount Then myCount = myCount + 1
        Next i
     
        CountChar = myCount
     
    End Function
    Used like:

    Sheet3
    AB
    20name; name; name2
    Excel 2007

    Worksheet Formulas
    CellFormula
    B20=countchar(A20, ";")



    Regards
    Adam
    Adam James
    Enthusiastic Amateur

    Follow me on Twitter, for no good reason!

    Useful posting advice for which I take no credit:
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker, Boders-Copy-Paste.
    If posting VBA code, please use Code Tags - like this [code]Paste your code here[/code] - more details here.

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Posts
    55

    Default Re: Count occurrences of character in cell

    I can not find the countchar function. Is it some kind of plugin? Differences in versions (I got excel2010)?

  5. #5
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    61,477

    Default Re: Count occurrences of character in cell

    HTH, Peter
    Please test any code on a copy of your workbook.

  6. #6
    Board Regular
    Join Date
    Feb 2012
    Posts
    55

    Default Re: Count occurrences of character in cell

    I did miss your post. It works! Thank you very much!

  7. #7
    Board Regular adam087's Avatar
    Join Date
    Jun 2010
    Location
    So'ton, UK
    Posts
    664

    Default Re: Count occurrences of character in cell

    Quote Originally Posted by peter_sjogarde View Post
    I can not find the countchar function. Is it some kind of plugin? Differences in versions (I got excel2010)?
    Vog's solution is much more elegant than mine. You should use that.

    For info only though - the code I posted in the same post needs to be coppied in to a module in the VB editor: Press ALT+F11, Right Click on 'VBA Project()' and Insert -> Module. Copy in the code. You can then close this window and use the CountChar function in this workbook.

    Regards
    Adam
    Adam James
    Enthusiastic Amateur

    Follow me on Twitter, for no good reason!

    Useful posting advice for which I take no credit:
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker, Boders-Copy-Paste.
    If posting VBA code, please use Code Tags - like this [code]Paste your code here[/code] - more details here.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com