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

 

[CalcSux78]

Welcome to the forum...

Not sure if you're comfortable with VBA coding, but a custom function would be able to find the value using a SPLIT function.

Excel 2010
	A	B	C
1	0001100001000001000000	6	5
2	001001000100	3	2
3	001001001001	2	2
Sheet1

Cell Formulas
Range	Formula
B1	=ZEROS(A1,1)
C1	=ZEROS(A1,2)

Function ZEROS(ByVal str As String, ByVal i As Integer) As Integer
Dim Arr As Variant
Dim iArr() As Integer
Dim n%


If i = 0 Then i = 1
Arr = Split(str, 1)
ReDim iArr(LBound(Arr) To UBound(Arr))
For n = LBound(Arr) To UBound(Arr)
    iArr(n) = Len(Arr(n))
Next n




ZEROS = WorksheetFunction.Large(iArr, i)
End Function

 

[JimboP77]

Some code like this may work...

Sub find_Zeros()

Range("b1").Select


Do


x = ActiveCell.Offset(0, -1)


Z = 1


Do Until Z > Len(x)






If Mid(x, Z, 1) = 0 Then


    Z = Z + 1
    
 T = 0
    Do Until Mid(x, Z, 1) <> 0
    
    T = T + 1
    Z = Z + 1
    
    Loop
   
End If


If T <> 0 Then


  If T + 1 > R Then
    R = T + 1
  End If
End If


Z = Z + 1
  
  Loop


ActiveCell = R


R = 0
T = 0


ActiveCell.Offset(1).Select




Loop Until ActiveCell.Offset(0, -1) = ""








End Sub

 

[trebuh]

Unfortunately I can not use macros for this project,therefore looking for some kind of formula.
If it will be much easier I can convert the 0 and 1 to read as a text (a and b)...so maybe much easier to find a string of characters ??? Any ideas?

Many thanks

 

[István Hirsch]

Give this a try, both formula should be confirmed with Ctrl + ShiFt + Enter, not just Enter.

Excel Workbook
	A	B	C
2	1100001000001000000	6	5
3	1001000100	3	2
4	1001001001	2	2
Sheet

 

[trebuh]

The above is very helpful however does not work in all the cases,

For example ,using the above and giving the number : 100000100001000000
The results are: Largest:4 , second largest :4
But should be: 6 and 5

For: 1001001 , gives me : 2 and 0 ,but should be 2 and 2

Any ideas....

 

[István Hirsch]

Give this a try:

Excel Workbook
	A	B	C
1	*	*	*
2	1100001000001000000	6	5
3	1001000100	3	2
4	1001001001	2	2
5	100000100001000000	6	5
6	1001001	2	2
7	1001000100	3	2
8	1001001001	2	2
9	100000100001000000	6	5
Sheet

 

[István Hirsch]

Try this in B2 (C2 should be the same as in post #7:

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

(confirmed with Ctrl + Shift + Enter)

 

[Rick Rothstein]

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).

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

I read the first red highlighted text as saying the two values cannot be the same. Given that, please explain why the second value equals the first in the above example.

 

[trebuh]

Yes my fault.What I was planning to say the calculation should count 1 string of zeros only once.
Example: 0001
Answer sholud be : 3 and 2nd largest:0(nil)
Example 2:
00001000
answer 4 and 3