# Arrayformula to get the max number of consecutive occurrence of a character inside a string

I have strings of different length like the one below:

LWLLLLXWLWWWXWWWXWWLWLWLWWWWLWLLLXXWLLLLL

From this string, I'd like to extract the maximum number of consecutive occurrence of `L`. Here it would return `5` because the max is the one in bold.

I was able to come with a formula I have to put on every row (where A2 is the string and B2 is the character, `L` in the example):

``````=IFERROR(ARRAYFORMULA(MAX(LEN(IFNA(REGEXEXTRACT(A2,B2&"{"&SEQUENCE(LEN(A2)-LEN(SUBSTITUTE(A2,B2,)),1,LEN(A2)-LEN(SUBSTITUTE(A2,B2,)),-1)&"}"),)))),)
``````

But I am looking for a unique `=ArrayFormula()` formula to avoid replicating this formula on every rows.

You'll find an example to play with following the link below:

Use `split(A2:A, "XW", true)` and `query()`, like this:

``````=arrayformula(
transpose(
query(
transpose( len( split(A2:A, "XW", true) ) ),
"select " & join( ", ", "max(Col" & sequence(rows(A2:A)) & ")" ) &
"label " & join( ", ", "max(Col" & sequence(rows(A2:A)) & ") ''" ),
0
)
)
)
``````
• I don't really understand how the formula works but it works... Thanks! Nov 18, 2021 at 9:17
• The formula is essentially doing a `max(len(split()))`. The verbose bits in the formula are there to meet your requirement of iterating all the values down the column in one go. Nov 18, 2021 at 15:48