+ Reply to Thread
Results 1 to 7 of 7

counting instances of a character within a string

  1. #1
    mikelee101
    Guest

    counting instances of a character within a string

    Hello,
    I'm trying to count the number of times a character appears within a string.
    I thought there was a function that could do this (i.e. one line of code),
    but can't seem to find it.

    For example, if I have a cell that contains "27,45,8,19,13" and want to
    count the commas, I'm looking for something like this:

    Num = CountChars("," , Cell.Value)
    where CountChars is the mystery function that I'm seeking.

    I can get the result I want with the Search function and a Do...Loop, but if
    there was a function it would make it much cleaner.

    Thanks.

    Mike

  2. #2
    Chip Pearson
    Guest

    Re: counting instances of a character within a string

    Mike,

    Try

    Function CountChars(Txt As String, Char As String) As Integer
    CountChars = Len(Txt) - Len(Replace(Txt, Char, ""))
    End Function

    --
    Cordially,
    Chip Pearson
    Microsoft MVP - Excel
    Pearson Software Consulting, LLC
    www.cpearson.com



    "mikelee101" <[email protected]> wrote in
    message
    news:[email protected]...
    > Hello,
    > I'm trying to count the number of times a character appears
    > within a string.
    > I thought there was a function that could do this (i.e. one
    > line of code),
    > but can't seem to find it.
    >
    > For example, if I have a cell that contains "27,45,8,19,13" and
    > want to
    > count the commas, I'm looking for something like this:
    >
    > Num = CountChars("," , Cell.Value)
    > where CountChars is the mystery function that I'm seeking.
    >
    > I can get the result I want with the Search function and a
    > Do...Loop, but if
    > there was a function it would make it much cleaner.
    >
    > Thanks.
    >
    > Mike




  3. #3
    mikelee101
    Guest

    Re: counting instances of a character within a string

    Ahhhhhh....makes sense.

    Thanks, Chip. I don't know if that would have ever occurred to me.

    Thanks again.

    Mike


    "Chip Pearson" wrote:

    > Mike,
    >
    > Try
    >
    > Function CountChars(Txt As String, Char As String) As Integer
    > CountChars = Len(Txt) - Len(Replace(Txt, Char, ""))
    > End Function
    >
    > --
    > Cordially,
    > Chip Pearson
    > Microsoft MVP - Excel
    > Pearson Software Consulting, LLC
    > www.cpearson.com
    >
    >
    >
    > "mikelee101" <[email protected]> wrote in
    > message
    > news:[email protected]...
    > > Hello,
    > > I'm trying to count the number of times a character appears
    > > within a string.
    > > I thought there was a function that could do this (i.e. one
    > > line of code),
    > > but can't seem to find it.
    > >
    > > For example, if I have a cell that contains "27,45,8,19,13" and
    > > want to
    > > count the commas, I'm looking for something like this:
    > >
    > > Num = CountChars("," , Cell.Value)
    > > where CountChars is the mystery function that I'm seeking.
    > >
    > > I can get the result I want with the Search function and a
    > > Do...Loop, but if
    > > there was a function it would make it much cleaner.
    > >
    > > Thanks.
    > >
    > > Mike

    >
    >
    >


  4. #4
    Kleev
    Guest

    Re: counting instances of a character within a string

    Or you could use:
    =LEN(D4)-LEN(SUBSTITUTE(D4,",",""))
    I believe the first time I saw this used was in John Walkenbach's (sp?) book.

    "mikelee101" wrote:

    > Ahhhhhh....makes sense.
    >
    > Thanks, Chip. I don't know if that would have ever occurred to me.
    >
    > Thanks again.
    >
    > Mike
    >
    >
    > "Chip Pearson" wrote:
    >
    > > Mike,
    > >
    > > Try
    > >
    > > Function CountChars(Txt As String, Char As String) As Integer
    > > CountChars = Len(Txt) - Len(Replace(Txt, Char, ""))
    > > End Function
    > >
    > > --
    > > Cordially,
    > > Chip Pearson
    > > Microsoft MVP - Excel
    > > Pearson Software Consulting, LLC
    > > www.cpearson.com
    > >
    > >
    > >
    > > "mikelee101" <[email protected]> wrote in
    > > message
    > > news:[email protected]...
    > > > Hello,
    > > > I'm trying to count the number of times a character appears
    > > > within a string.
    > > > I thought there was a function that could do this (i.e. one
    > > > line of code),
    > > > but can't seem to find it.
    > > >
    > > > For example, if I have a cell that contains "27,45,8,19,13" and
    > > > want to
    > > > count the commas, I'm looking for something like this:
    > > >
    > > > Num = CountChars("," , Cell.Value)
    > > > where CountChars is the mystery function that I'm seeking.
    > > >
    > > > I can get the result I want with the Search function and a
    > > > Do...Loop, but if
    > > > there was a function it would make it much cleaner.
    > > >
    > > > Thanks.
    > > >
    > > > Mike

    > >
    > >
    > >


  5. #5
    Kleev
    Guest

    Re: counting instances of a character within a string

    Never mind. Didn't read the post carefully enough (or remember which forum I
    was in.)

    "Kleev" wrote:

    > Or you could use:
    > =LEN(D4)-LEN(SUBSTITUTE(D4,",",""))
    > I believe the first time I saw this used was in John Walkenbach's (sp?) book.
    >
    > "mikelee101" wrote:
    >
    > > Ahhhhhh....makes sense.
    > >
    > > Thanks, Chip. I don't know if that would have ever occurred to me.
    > >
    > > Thanks again.
    > >
    > > Mike
    > >
    > >
    > > "Chip Pearson" wrote:
    > >
    > > > Mike,
    > > >
    > > > Try
    > > >
    > > > Function CountChars(Txt As String, Char As String) As Integer
    > > > CountChars = Len(Txt) - Len(Replace(Txt, Char, ""))
    > > > End Function
    > > >
    > > > --
    > > > Cordially,
    > > > Chip Pearson
    > > > Microsoft MVP - Excel
    > > > Pearson Software Consulting, LLC
    > > > www.cpearson.com
    > > >
    > > >
    > > >
    > > > "mikelee101" <[email protected]> wrote in
    > > > message
    > > > news:[email protected]...
    > > > > Hello,
    > > > > I'm trying to count the number of times a character appears
    > > > > within a string.
    > > > > I thought there was a function that could do this (i.e. one
    > > > > line of code),
    > > > > but can't seem to find it.
    > > > >
    > > > > For example, if I have a cell that contains "27,45,8,19,13" and
    > > > > want to
    > > > > count the commas, I'm looking for something like this:
    > > > >
    > > > > Num = CountChars("," , Cell.Value)
    > > > > where CountChars is the mystery function that I'm seeking.
    > > > >
    > > > > I can get the result I want with the Search function and a
    > > > > Do...Loop, but if
    > > > > there was a function it would make it much cleaner.
    > > > >
    > > > > Thanks.
    > > > >
    > > > > Mike
    > > >
    > > >
    > > >


  6. #6

    Re: counting instances of a character within a string

    great work chip, it's alwaqys the simple functions that are the best!
    very effective, thanksyou.


    Kleev wrote:
    > Never mind. Didn't read the post carefully enough (or remember which forum I
    > was in.)
    >
    > "Kleev" wrote:
    >
    > > Or you could use:
    > > =LEN(D4)-LEN(SUBSTITUTE(D4,",",""))
    > > I believe the first time I saw this used was in John Walkenbach's (sp?) book.
    > >
    > > "mikelee101" wrote:
    > >
    > > > Ahhhhhh....makes sense.
    > > >
    > > > Thanks, Chip. I don't know if that would have ever occurred to me.
    > > >
    > > > Thanks again.
    > > >
    > > > Mike
    > > >
    > > >
    > > > "Chip Pearson" wrote:
    > > >
    > > > > Mike,
    > > > >
    > > > > Try
    > > > >
    > > > > Function CountChars(Txt As String, Char As String) As Integer
    > > > > CountChars = Len(Txt) - Len(Replace(Txt, Char, ""))
    > > > > End Function
    > > > >
    > > > > --
    > > > > Cordially,
    > > > > Chip Pearson
    > > > > Microsoft MVP - Excel
    > > > > Pearson Software Consulting, LLC
    > > > > www.cpearson.com
    > > > >
    > > > >
    > > > >
    > > > > "mikelee101" <[email protected]> wrote in
    > > > > message
    > > > > news:[email protected]...
    > > > > > Hello,
    > > > > > I'm trying to count the number of times a character appears
    > > > > > within a string.
    > > > > > I thought there was a function that could do this (i.e. one
    > > > > > line of code),
    > > > > > but can't seem to find it.
    > > > > >
    > > > > > For example, if I have a cell that contains "27,45,8,19,13" and
    > > > > > want to
    > > > > > count the commas, I'm looking for something like this:
    > > > > >
    > > > > > Num = CountChars("," , Cell.Value)
    > > > > > where CountChars is the mystery function that I'm seeking.
    > > > > >
    > > > > > I can get the result I want with the Search function and a
    > > > > > Do...Loop, but if
    > > > > > there was a function it would make it much cleaner.
    > > > > >
    > > > > > Thanks.
    > > > > >
    > > > > > Mike
    > > > >
    > > > >
    > > > >



  7. #7
    Registered User
    Join Date
    02-25-2016
    Location
    Atlanta
    MS-Off Ver
    365
    Posts
    1

    Re: counting instances of a character within a string

    Just for the record, I was surprised to discover that using Split and Ubound/Lbound was very close to using Len/Replace. Below is the code I used for the different tests, the ratio of the means of the two methods over 10 samples, and the 10 samples below in the table.

    Mean(Split) ÷ Mean(Replace) ≈ 1.039054

    Split:
    Please Login or Register  to view this content.
    Replace:
    Please Login or Register  to view this content.
    Split
    Replace
    1
    891
    828
    2
    906
    859
    3
    891
    844
    4
    906
    844
    5
    969
    984
    6
    1062
    1047
    7
    922
    890
    8
    922
    844
    9
    1000
    1047
    10
    1109
    1031
    Mean
    957.8
    921.8
    Last edited by oghaki; 03-17-2019 at 12:48 AM. Reason: formatting

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1