# Excel Question

#### ststern45

##### Well-known Member
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

Last edited:

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Try:-
Code:
``````[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

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
ABCDE
1Count what?
23  3
3322
42
52
62
70
80
90
100
110
120
13311
141
151
161
171
183
193
203
213
223
233
243
253
263
273
283
293
303
3131414
Sheet605
Cell Formulas
RangeFormula
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))

Thanks so much.

Works like a charm!!

@ststern45
You have been given two completely different solutions.
For reference, could you please indicate which of them
Works like a charm!!
Cheers

Replies
6
Views
127
Replies
1
Views
199
Replies
2
Views
371
Replies
16
Views
523
Replies
3
Views
115

1,203,521
Messages
6,055,885
Members
444,830
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back