Excel Help Number of Digits

[Cartref45]

Hi,

Can someone help with an Excel formula.

Let's say that I have 231141 in a cel, how can I retrieve how many "1s" in this string

Thanks in Advance

Cartref45

 

[Frank]

I’m on holiday laid on a beach so I can’t test this in Excel, but from memory, try this if counting “1”s :-
If you have your number in cell A1

=LEN(TEXT(A1,”0”))-LEN(SUBSTITUTE(TEXT(A1,”0”),"1",""))
Should all appear on one line not two..

Best I can do without Excel in front of me.
All the best, Frank

 

[Cartref45]

Hi Frank,

Sorry to disturb your slumber in the sun, lucky you.

I copy and pasted your formula into my sheet but it returned #NAME?

It can wait until you return from your holiday, in the meantime I'll play about with what you've given me

Thanks Cartref45

 

[Frank]

Try this version, format the cell as general or text first, it might work. It’s looking for a text version of number 1.

[FONT=&quot]=LEN(A1)-LEN(SUBSTITUTE(A1,"1",""))
[/FONT]

[FONT=&quot] [/FONT]

 

[Cartref45]

Frank,

I played around with your suggestion and this appears to work =LEN(A1)-LEN(SUBSTITUTE(A1,"1",""))

Wouldn't have got there without your suggestion

THanks

Cartref45

 

[bruks]

Hi Frank!
If you use a simple spreadsheet Excel, let's do the follow:
1) The first four numbers “Lotto Max” 7/49 such as: 6, 23, 24, 42 I picked up: https://lottery.olg.ca/en-ca/winning-numbers/lotto-max/have-your-numbers-won?num1=5&num2=11&num3=22&num4=26&num5=1&num6=2&num7=3
2) You need to set the numbers 1, 2, 3 that is the 5th, 6th and 7th numbers so that the win «Your numbers won «____» times» , was more than 48 times.
https://lottery.olg.ca/en-ca/winning-numbers/lotto-max/have-your-numbers-won?
You can see the steps of the calculation method in Excel.
Do You have the coordinates of a Registered User larbec: http://www.lottoforums.com/lottery/showthread.php/13271-The-Kob-7-system-for-Lotto-Max

Bruks