Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    How to count instances of a character within a cel (2000/2003/2007)

    I tried searching on this but did not find any usseful links until after I came up with the solution on my own and even then I found only 1 link on Google for this.

    Question: How to count the instances of a character within a cell.

    Answer: While there are many tips on the net on how to count the number of cells meeting some crtieria, counting the number of instaces of a character in a cell is a different story. The following formula can be used to do just this. While I did come up with the below on my own, afterwards I found a link on Google where someone else had al;ready come up with the same formula. SO while I did come up with this on my own I was not the first to do so and therefore do not take credit for originating it. I'm hoping posting this here will help others more easily find this should they need it.

    In the below formula,
    N represents the character you are searching for and cell A1 contains text which has 1 or more instances of 'N'.

    =Len(A1) - Len(Substitute(A1,N,"")

    A more detailed example, lets assume that cell A1 contains the text "Hello World" and you want to count how many instances of the letter 'l' are in the cell. The formula would look like this:

    =Len(A1) - Len(Substitute(A1,"l","")

    This formula would return the value 3. You can also use this formula to count the number of special/non-printing characters are in a cell. For example to count how many instaces of the non-printing character CHAR(160) are in cell A1 you would use the following formula:

    =Len(A1) - Len(Substitute(A1,CHAR(160),"")

    Ed

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to count instances of a character within a

    Hi Ed

    Very useful <img src=/S/cool.gif border=0 alt=cool width=15 height=15>, you are however missing the last parenthesis ")" on the 3 formulas to work properly
    Jerry

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: How to count instances of a character within a cel (2000/2003/2007)

    This is a relatively common use of substitute and you can find many instances on this board by searching len substitute.

    A more generic one is:

    =Len(A1) - Len(Substitute(A1,N,""))/Len(N)

    in case the substring you are searching is more than one letter. In VB you must use something like:

    iCount = (Len(sTemp) - len(Application.worksheetfunction.substitute(sTemp , str, ""))) / len(str)

    since the substitute is not part of VB.

    <!profile=HansV>HansV<!/profile> also used it in an array formula to [Count instances of a letter in a range of cells (2003) instead of a single cell.

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to count instances of a character within a

    Jezaa,

    ARRRRGGGHHHH( done in the sound of the typical South Atlantic Pirate-nese) you be right about that blatsed paren. I guess it walked it the plank [img]/forums/images/smilies/smile.gif[/img]

    Ed

  5. #5
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to count instances of a character within a cel (2000/2003/2007)

    Steve,
    The use of len substitue does help locate many examples of this however you have to know part of the solution to know to use these keywords. I added the post with the specific wording I did so as to make it easier for others to find this same answer. It seems like too often the right keywords aren't located with a piece of info unless it is in response to a question from another as is the case with these forums.

    Thanks
    Ed

Posting Permissions

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