Excel Question

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
798
Office Version
  1. 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:

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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":

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Count what?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">3</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">2</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">2</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">2</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">0</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">1</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">1</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">1</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">1</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">3</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;">3</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;">3</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;">3</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;">3</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;;">3</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="text-align: right;;">3</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="text-align: right;;">3</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style="text-align: right;;">3</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style="text-align: right;;">3</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style="text-align: right;;">3</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style="text-align: right;;">3</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">30</td><td style="text-align: right;;">3</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">31</td><td style="text-align: right;;">3</td><td style="text-align: right;;">14</td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet605</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">A2<>3,AND(<font color="Green">A2=3,A3=3</font>)</font>),"",COUNTIF(<font color="Red">A$2:A2,3</font>)-SUM(<font color="Red">B$1:B1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">A2<>E$2,AND(<font color="Green">A2=E$2,A3=E$2</font>)</font>),"",COUNTIF(<font color="Red">A$2:A2,E$2</font>)-SUM(<font color="Red">C$1:C1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
798
Office Version
  1. 2010
Platform
  1. Windows
Thanks so much.

Works like a charm!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,520
Office Version
  1. 365
Platform
  1. Windows
@ststern45
You have been given two completely different solutions.
For reference, could you please indicate which of them
Works like a charm!!
Cheers
 

Watch MrExcel Video

Forum statistics

Threads
1,114,652
Messages
5,549,210
Members
410,905
Latest member
Extjel
Top