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:
https://docs.google.com/spreadsheets/d/1OZ6HAx0Z317fRyjjEVYphZNrlXdwc7s3z7vMc05HdoI/edit?usp=sharing