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

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
    Nov 18 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. Nov 18 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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