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]

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

Given that it was a response to the question I asked in Message #10, I misread what you said in Message #11 as meaning the second number should be 0 if it was equal to the first number. Here are my two formulas that will do what you said...

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

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

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

With that said, it looks like the formula XOR LX posted in Message #18 would be better to use in cell B2 instead of what I posted.

 

[trebuh]

Given that it was a response to the question I asked in Message #10, I misread what you said in Message #11 as meaning the second number should be 0 if it was equal to the first number. Here are my two formulas that will do what you said...

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

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

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

With that said, it looks like the formula XOR LX posted in Message #18 would be better to use in cell B2 instead of what I posted.

Dear Rick,

Many thanks for that. Formulas from Your last post works perfectly!
Thank You and the others to spent Your time to help me. Great job!!