1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Total count of numbers in each cell

Discussion in 'Ask an Excel Question' started by yaparala, Aug 12, 2017.

  1. yaparala

    yaparala New Member

    Messages:
    26
    Hi All,

    In attached excel B2 cell having some values with contain special characters like "," and space.
    i want total count each cell wise


    Number
    1,2,3,4,5
    1,2,
    ,3,
    ,4 , , 6,, ,
    , ,

    output should be like
    5
    2
    1
    2
    0

    Please refer attached excel sheet.

    Thanks,
    Sreeni

    Attached Files:

  2. AliGW

    AliGW Member

    Messages:
    68
    In C2 copied down:

    =LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,",",""),".","")," ",""))
    Ufoo and Thomas Kuriakose like this.
  3. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,636
    Hi ,

    When you say number , do you mean digit ?

    Will all 'numbers' be single digit numbers ?

    Narayan
  4. yaparala

    yaparala New Member

    Messages:
    26
    Thanks AliGW :)
  5. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,699
    just to offer an alternative approach

    C2:
    =SUMPRODUCT((CODE(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1))>=48)*(CODE(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1))<=57))

    or

    =SUMPRODUCT((MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)>="0")*(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)<="9"))

    Copy down
    Thomas Kuriakose and Ufoo like this.
  6. AliGW

    AliGW Member

    Messages:
    68
    You're welcome! :)
  7. Naresh Yadav

    Naresh Yadav Active Member

    Messages:
    148
    Hi,

    you can also try this one to get your resule

    =COUNT(INDEX(1/MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),,))
    Ufoo likes this.
  8. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,248
    =COUNT(INDEX(1/MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),,))

    The above formula will give wrong result if the string contains 0.

    Example, A1: A1B0C3, the formula returned 2, it should be 3

    ( because 1/0 = #DIV/0!, of which will not be counted )

    To fix this problem, just modified it into :

    =COUNT(INDEX(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),))

    Regards
    Bosco
    Last edited: Aug 13, 2017
    Thomas Kuriakose and NARAYANK991 like this.
  9. Naresh Yadav

    Naresh Yadav Active Member

    Messages:
    148
    thank you so much Sir, for giving attention and rectify my mistake

    regards Naresh

Share This Page