Count maximum consecutive characters within a cell

[eat at joe's]

I have a database with a column containing a series of results - either "x" or "."
I need a formula that counts the maximum consecutive occurrences of "." for each person.

John .xxxx
Mary ..x...
Lisa ...xx
Tom .x....
John x.....
Tom .x.xx.
Lisa x.x..x
Mary ......

so the result for john's first entry should be 1, mary's first entry should be 3. Mary's second entry should be 6. If the names are in column A, the results in Column B, I'd like the formula result to appear in column C.

thanks

Thanks very much

 

[Domenic]

Assuming that A2:B8 contains the data, try...

C2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=MAX(FREQUENCY(IF(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)=".",ROW(INDIRECT("1:"&LEN(B2)))),IF(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)<>".",ROW(INDIRECT("1:"&LEN(B2))))))

 

[Ron Coderre]

If the maximum number of periods is 6,
try this regular formula:

=LOOKUP(2,1/COUNTIF(A1,"*"&{".","..","...","....",".....","......"}&"*")
,{1,2,3,4,5,6})

But, if there can be any number of consecutive periods,
try this regular formula:

=LOOKUP(2,1/COUNTIF(A1,"*"&REPT(".",ROW(INDIRECT("1:"&LEN(A1))))&"*"),
ROW(INDIRECT("1:"&LEN(A1))))

Is that some thing you can work with?

 

[T. Valko]

But, if there can be any number of consecutive periods,
try this regular formula:

=LOOKUP(2,1/COUNTIF(A1,"*"&REPT(".",ROW(INDIRECT("1:"&LEN(A1))))&"*"),
ROW(INDIRECT("1:"&LEN(A1))))

Is that some thing you can work with?

Nice one, Ron!

I came up with this tweak that will alow it to also count digits in a numeric string.

=LOOKUP(2,1/SEARCH(REPT([char],ROW(INDIRECT("1:"&LEN(A1)))),A1),ROW(INDIRECT("1:"&LEN(A1))))

Where [char] is the character or digit of interest.

10111001110

Max number of consecutive 1s:

=LOOKUP(2,1/SEARCH(REPT(1,ROW(INDIRECT("1:"&LEN(A1)))),A1),ROW(INDIRECT("1:"&LEN(A1))))

00XXXXX00XXX

Max number of consecutive Xs:

=LOOKUP(2,1/SEARCH(REPT("x",ROW(INDIRECT("1:"&LEN(A1)))),A1),ROW(INDIRECT("1:"&LEN(A1))))

 

[Ron Coderre]

Nice touch...replacing the COUNTIF with SEARCH to eliminate tne need for wildcards.

 

[T. Valko]

Nice one, Ron!

I came up with this tweak that will alow it to also count digits in a numeric string.

=LOOKUP(2,1/SEARCH(REPT([char],ROW(INDIRECT("1:"&LEN(A1)))),A1),ROW(INDIRECT("1:"&LEN(A1))))

Where [char] is the character or digit of interest.

10111001110

Max number of consecutive 1s:

=LOOKUP(2,1/SEARCH(REPT(1,ROW(INDIRECT("1:"&LEN(A1)))),A1),ROW(INDIRECT("1:"&LEN(A1))))

00XXXXX00XXX

Max number of consecutive Xs:

=LOOKUP(2,1/SEARCH(REPT("x",ROW(INDIRECT("1:"&LEN(A1)))),A1),ROW(INDIRECT("1:"&LEN(A1))))

If we use a bigger lookup value then we can eliminate the division operation:

=LOOKUP(1E100,SEARCH(REPT([char],ROW(INDIRECT("1:"&LEN(A1)))),A1),ROW(INDIRECT("1:"&LEN(A1))))

 

[Rick Rothstein]

Here is an array-entered** formula that seems to work...

=MAX(IF(ISNUMBER(FIND(REPT(".",ROW(1:99)),A1)),LEN(REPT(".",ROW(1:99)))))

Note that this formula, as written, assumes you will never insert any rows within the first 99 rows of the worksheet. If you might have to do a row insertion in the future, then use this longer version of this formula instead...

=MAX(IF(ISNUMBER(FIND(REPT(".",ROW(INDIRECT("1:99"))),E19)),LEN(REPT(".",ROW(INDIRECT("1:99"))))))

Note that both versions ofthe formula assume the total number of dots and exes will never be more than 99 within the cell. If there could be more, just increase the two 99's to a number larger than the largest possible number of them. By the way, if the maximum number of dots and exes will be significantly less than 99, by all means, change the 99's to that number instead... it will make the formulas more efficient.

**Commit these formulas using CTRL+SHIFT+ENTER and not just Enter by itself

 

[Haseeb Avarakkan]

Hi, Try also,

=MATCH(1E+100,SEARCH(REPT(".",ROW(INDEX(A:A,1):INDEX(A:A,LEN(B1)))),B1))

Confirmed with Control+Shift+Enter.

 

[Ron Coderre]

Why even consider an array-formula in this situation when
a) Most typical users have no idea how to maintain them
b) Something as relatively simple as this will work:
=LOOKUP(2,1/SEARCH(REPT(".",ROW($1:$99)),A1),ROW($1:$99))

 

[Rick Rothstein]

Why even consider an array-formula in this situation

1) It was the first thought that came to mind when I read the question.

2) I like array-formulas.