google-sheetsnsregularexpression

REGEXEXTRACT to capture group of strings between 2 characters


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

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

Expected output group 3

Expected output group 4


Solution

  • Let me know if this works for you!

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

    enter image description here