How many zero values in a string

[trebuh]

Hello.I'm new one here. Could anybody help me with this?

I have a string of numbers in one cell (only 0 and 1)
I'm looking for formula which count the longest string of 0 in this string and then second longest after this (but it could not be the same).

Both answers could be in separate cells (let's say Longest one in A2 , and second longest in A3)

Example:
I have a number in cell A1: 0001100001000001000000

The answer for the longest string of zeros should be: 6
The second longest string should be: 5

2nd Example:
Number: 001001000100
Longest: 3
2nd longest: 2

3rd example:
Number: 001001001001
Longest: 2
2nd longest: 2

Many thanks

 

[Rick Rothstein]

With your data in Column A starting on Row 2, put these array-entered** formulas in the indicated cells and copy them down to the end of your data...

B2: =MAX(LEN(TRIM(MID(SUBSTITUTE(SUBSTITUTE(" "&$A2,1," ")," ",REPT(" ",99)),ROW($1:$99)*99,99))))

C2: =0+SUBSTITUTE(" "&LARGE(LEN(TRIM(MID(SUBSTITUTE(SUBSTITUTE(" "&$A2,1," ")," ",REPT(" ",99)),ROW($1:$99)*99,99))),2)&" "," "&B2&" ",0)

**Commit these formulas using CTRL+SHIFT+ENTER and not just Enter by itself.

 

[trebuh]

The above formulas works only for a short numbersI
have tried:
100000000000011111000000111111111111111111111111
and shows me : 6 and 6
Suppose to be 12 and 6

Any ideas?

 

[Rick Rothstein]

The above formulas works only for a short numbersI
have tried:
100000000000011111000000111111111111111111111111
and shows me : 6 and 6
Suppose to be 12 and 6

You should really "Reply With Quote" when you have multiple responders (make sure to remove the parts of the message that have nothing to do with your answer though, helps with disk space on this forums servers), otherwise we cannot be sure who you are replying to.

If you are replying to the formulas I posted in Message #11, I cannot replicate what you posted... I get 12 from the first formula and 6 from the second one. I will say, though, that there is a limit for my formulas (as written... can be increased) of 99 digits. If your numbers can be longer than 99 digits, then if you can give us an indication of how larger a "number" will be placed in your cells, I will adjust the formulas for you.

 

[trebuh]

True. This answer was for another post.
Basically I have 48 digit numbers (each one) and there are different combinations of 0 and 1.

You should really "Reply With Quote" when you have multiple responders (make sure to remove the parts of the message that have nothing to do with your answer though, helps with disk space on this forums servers), otherwise we cannot be sure who you are replying to.

If you are replying to the formulas I posted in Message #11, I cannot replicate what you posted... I get 12 from the first formula and 6 from the second one. I will say, though, that there is a limit for my formulas (as written... can be increased) of 99 digits. If your numbers can be longer than 99 digits, then if you can give us an indication of how larger a "number" will be placed in your cells, I will adjust the formulas for you.

 

[Aladin Akyurek]

In B1 control+shift+enter, not just enter, copy across to C1, and down:

=LARGE(FREQUENCY(IF(MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1)="0",ROW(INDIRECT("1:"&LEN($A1)))),IF(1-(MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1)="0"),ROW(INDIRECT("1:"&LEN($A1))))),COLUMNS($B1:B1))

 

[Rick Rothstein]

True. This answer was for another post.
Basically I have 48 digit numbers (each one) and there are different combinations of 0 and 1.

So, did the formulas I posted in Message #11 do what you want?

 

[István Hirsch]

.. or give this a try (both should be confirmed with Ctrl + Shift + Enter):

B2:

=50-MATCH(TRUE,ISNUMBER(FIND(REPT(0,50-ROW($1:$49)),A2)),0)

C2:

=IFERROR(50-MATCH(TRUE,ISNUMBER(FIND(REPT(0,50-ROW($1:$49)),SUBSTITUTE(A2,REPT(0,B2),""))),0),0)

 

[XOR LX]

=50-MATCH(TRUE,ISNUMBER(FIND(REPT(0,50-ROW($1:$49)),A2)),0)

Or:

=MATCH(1,0/FIND(REPT(0,ROW($1:$48)),A2))

Regards

 

[trebuh]

So, did the formulas I posted in Message #11 do what you want?

I have used a different combinations of formulas from above posts (all entered with CTRL+SHIFT+ENTER)
This one below seems to be the most efficient.

B2 {=MATCH(1,0/FIND(REPT(0,ROW($1:$46)),A2))}
C2 {=IFERROR(50-MATCH(TRUE,ISNUMBER(FIND(REPT(0,50-ROW($1:$47)),SUBSTITUTE(A2,REPT(0,B2)," "))),0),0)}

However, It works in most of the cases but I have noticed that when I have 2 the same strings of zeros it does not work properlyexample a number)
A2 : 0000000011110000000011111

It gives me an answer: 8 and 0 (instead of 8 and 8)

Similar story with formula from Post # 17
B4 {=50-MATCH(TRUE,ISNUMBER(FIND(REPT(0,50-ROW($1:$49)),A4)),0)}
C4 {=IFERROR(50-MATCH(TRUE,ISNUMBER(FIND(REPT(0,50-ROW($1:$49)),SUBSTITUTE(A4,REPT(0,B4),""))),0),0)}
A4 : 000000001111111111000000001111111111111111111111
gives me answer : 8 and 0 (instead of 8 and 8)

Formula used from post #11 (the same results as above)
B2 {=MAX(LEN(TRIM(MID(SUBSTITUTE(SUBSTITUTE(" "&$A2,1," ")," ",REPT(" ",99)),ROW($1:$99)*99,99))))}
C2 {=0+SUBSTITUTE(" "&LARGE(LEN(TRIM(MID(SUBSTITUTE(SUBSTITUTE(" "&$A2,1," ")," ",REPT(" ",99)),ROW($1:$99)*99,99))),2)&" "," "&B2&" ",0)}
A2: 000000001111111111000000001111111111111111111111
Answer is 8 and 0 , suppose to be 8 and 8

Regards,

 

[Aladin Akyurek]

Did you consider evaluating the formula for the generic consecutive count, using a definition like below?

Rvec which can be defined as referring to:

=ROW(Sheet1!$1:$50)<strike></strike>

The LARGE formula becomes:

B2, control+shift+enter, copy across to C2, and down:

=LARGE(FREQUENCY(IF(MID($A1,Rvec,1)="0",Rvec),IF(1-(MID($A1,Rvec,1)="0"),Rvec)),COLUMNS($B1:B1))<strike></strike>