• Counting within a cell (97)

    Author
    Topic
    #379639

    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

    Viewing 1 reply thread
    Author
    Replies
    • #632801

      If the text is in A1, the formula

      =SUM(1*(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="n"))

      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”.

    • #632803

      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

      • #632813

        Thanks,

        It works perfectly

      • #633228

        Peter,

        There is no need for SUM…

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

        should suffice.

        Aladin

        • #633239

          Aladin

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

          Regards
          Peter

    Viewing 1 reply thread
    Reply To: Counting within a cell (97)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: