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

  • 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

yaparala

New Member
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
 

Attachments

  • Total count in each cell.xlsx
    10.9 KB · Views: 10
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
 
Hi,

you can also try this one to get your resule

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

=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:
=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
thank you so much Sir, for giving attention and rectify my mistake

regards Naresh
 
Back
Top