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. ### yaparalaNew 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

File size:
10.9 KB
Views:
10
2. ### AliGWMember

Messages:
67
In C2 copied down:

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

Messages:
15,367
Hi ,

When you say number , do you mean digit ?

Will all 'numbers' be single digit numbers ?

Narayan

Messages:
26
Thanks AliGW
5. ### HuiExcel NinjaStaff Member

Messages:
10,606
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. ### AliGWMember

Messages:
67
You're welcome!
7. ### Naresh YadavActive Member

Messages:
136
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_yipWell-Known Member

Messages:
1,222
=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 YadavActive Member

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

regards Naresh