I'm looking for a regex arrayformula which would be able to extract all possible groups from a list. here is a visualisation:

so far I tried:
=ArrayFormula(REGEXEXTRACT(A2:A6, "["&TEXTJOIN("|", 1, A8:A)&"]"))
=ArrayFormula(REGEXEXTRACT(A2:A6, "[("&TEXTJOIN("|", 1, A8:A)&")]"))
=ArrayFormula(REGEXEXTRACT(A2:A5, "["&TEXTJOIN("|", 1, "("&A8:A&")")&"]"))
...
you can fake it like this in B2:
=INDEX(IF(REGEXMATCH(A2:A5,
TRANSPOSE(SUBSTITUTE(A8:A10, " ", ".*"))), TRANSPOSE(A8:A10), ))

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With