0

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

1 Answer 1

1

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 
    ) 
  ) 
)
2
  • I don't really understand how the formula works but it works... Thanks!
    – Exdefoot
    Commented Nov 18, 2021 at 9:17
  • 1
    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. Commented Nov 18, 2021 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.