I have the following working gsheet formula that transposes, split, trims using REGEXEXTRACT which works to capture the first group of strings separated by OR

=transpose(split(IFERROR(TRIM(REGEXEXTRACT(A2,"([^\()] )")))," OR ",FALSE))

Example below using formula above

("AD" OR "AB" OR "ZZ" OR "Short Long" OR "Long Short") w/5 (("foo bar*" OR "bar* foo" OR "kit kat*") w/5 ("ping* pong game*" OR "ppg") w/5 ("bro" OR "sis" OR "fam"))

extracts the following for group 1

• "AD"
• "AB"
• "ZZ"
• "Short Long"
• "Long Short"

How to extract other groups that are in between w/5 string? Note that there can be multiple instances of w/5 from a given statement. Example above have 3.

Expected output group 2

• "foo bar*"
• "bar* foo"
• "kit kat*"

Expected output group 3

• "ping* pong game*"
• "ppg"

Expected output group 4

• "bro"
• "sis"
• "fam"

CodePudding user response：

Let me know if this works for you!

=TRANSPOSE(INDEX(SPLIT(INDEX(REGEXEXTRACT(TRANSPOSE(split(A2," w/5 ",FALSE)),"([^\()] )"))," OR ",false)))