Excel Question

[ststern45]

I have a excel question:

Is there a way to count the number of times a number repeats in a string before another

number replaces it.

I am talking about consecutive repeats within a string such as below.

What is the the longest run of 3 at any one time?

3
3
2
2
2
0
0
0
0
0
0
3
1
1
1
1
3
3
3
3
3
3
3
3
3
3
3
3
3
3

Thanks in advance!!

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG24Feb40
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]For[/COLOR] n = 1 To Len([A1])
    [COLOR="Navy"]If[/COLOR] Mid([A1], n, 1) = 3 [COLOR="Navy"]Then[/COLOR]
            c = c + 1
    [COLOR="Navy"]ElseIf[/COLOR] Not c = 0 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] c >= Temp [COLOR="Navy"]Then[/COLOR] Temp = c
        c = 0
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n

[COLOR="Navy"]If[/COLOR] c >= Temp [COLOR="Navy"]Then[/COLOR] Temp = c
MsgBox Temp
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jtakw]

Hi,

Here's a couple of ways using formula, need an Empty or Header row on top of your data.

Use B2 formula with your 3 hard-coded.
Use C2 formula using Cell reference for the criteria to count, you can change the criteria "on the fly":

Book1
	A	B	C	D	E
1					Count what?
2	3				3
3	3	2	2
4	2
5	2
6	2
7	0
8	0
9	0
10	0
11	0
12	0
13	3	1	1
14	1
15	1
16	1
17	1
18	3
19	3
20	3
21	3
22	3
23	3
24	3
25	3
26	3
27	3
28	3
29	3
30	3
31	3	14	14
Sheet605

Cell Formulas
Range	Formula
B2	=IF(OR(A2<>3,AND(A2=3,A3=3)),"",COUNTIF(A$2:A2,3)-SUM(B$1:B1))
C2	=IF(OR(A2<>E$2,AND(A2=E$2,A3=E$2)),"",COUNTIF(A$2:A2,E$2)-SUM(C$1:C1))

 

[ststern45]

Thanks so much.

Works like a charm!!

 

[Fluff]

@ststern45
You have been given two completely different solutions.
For reference, could you please indicate which of them

Works like a charm!!

Cheers