Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Amersfoort, Utrecht, Netherlands
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting within a cell (97)

    Hello all,

    Maybe I'm overlooking it, but I need a function that is doing the following:

    I have cells filled with a character-code like "anfdggrnfhsoofk" And I need to know how often the letter 'n' is in this cell

    Does this functions exist in Excel? and if not, how to solve it

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting within a cell (97)

    If the text is in A1, the formula<pre>=SUM(1*(MID(A1,ROW(INDIRECT("1:"&LEN(A 1))),1)="n"))</pre>

    entered as an array formula (confirm with Ctrl+Shift+Enter) should do what you want. You can replace "n" with the reference to a cell containing "n".

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting within a cell (97)

    Assuming the text is in Cell A1 and you are looking for the letter 'n'..

    =SUM(LEN(A1))-SUM(LEN(SUBSTITUTE(A1,"n","")))

    see http://www.emailoffice.com/excel/arrays-bobumlas.html for more info.

    Regards
    Peter

  4. #4
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Amersfoort, Utrecht, Netherlands
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting within a cell (97)

    Thanks,

    It works perfectly

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting within a cell (97)

    Peter,

    There is no need for SUM...

    =LEN(A1)-LEN(SUBSTITUTE(A1,"n",""))

    should suffice.

    Aladin
    Microsoft MVP - Excel

  6. #6
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting within a cell (97)

    Aladin

    Thanks for pointing that out... That'll teach me to take others code/functions at face value!

    Regards
    Peter

Posting Permissions

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