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
Let me know if this works for you!
=TRANSPOSE(INDEX(SPLIT(INDEX(REGEXEXTRACT(TRANSPOSE(split(A2," w/5 ",FALSE)),"([^\()]+)"))," OR ",false)))