Counting digits in a number.

[andrewmac]

Hi all,


Can anyone tell me if there is a formula to count the number of occurences of a single numeric in a number. E.g. in number 10210341 the answer for the number of 1s would be 3. The number may have any number of digits. Many thanks in advance.

 

[Luke M]

=LEN(A2)-LEN(SUBSTITUTE(A2,CriteriaValue,""))


If you want to find out how many 1's there are, formula would be:

=LEN(A2)-LEN(SUBSTITUTE(A2,1,""))

 

[SirJB7]

Hi, andrewmac!

Very nice exercise. And still unsolved, if it has a simple formula solution.

Up to now, this is the best I could get:

http://dl.dropbox.com/u/60558749/Counting%20digits%20in%20a%20number%20%28for%20andrewmac%20at%20chandoo.org%29.xlsx

Please keep us informed if you solve it.

Regards!

PS: Arrived at dessert-time, didn't see Luke M's post. Trying it now...

PS2: And even worse, I now remember having used it before :$

 

[prasaddn]

Here is one answer I got and surprisingly its working


=SUMPRODUCT(--((MID(B4,ROW(OFFSET($A$1,,,LEN(B4))),1)+0)=C4))


I have assumed the value is in B4 and the number you want to count is in C4.


idea pick from this link.. (no surprise its our chandoo only)

http://chandoo.org/wp/2011/03/18/calculating-sum-of-digits-in-a-number/


Regards,

Prasad DN

PS: Very interesting one I liked it..

 

[prasaddn]

I liked Luke's solution, simple technique.. and thats what excel is all about. Many complex problems gets solved with simple logics.


Regards,

Prasad DN.

 

[SirJB7]

@prasaddn

Hi!

I thought about an approach that included SP and I didn't reach at any useful point. Very nice resolution, I like it too. And I agree that Luke M's has the beauty of simpleness.

Regards!