Excel Question

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
974
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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!!
 
Last edited:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,224,269
Messages
6,177,566
Members
452,784
Latest member
talippo

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top