Split string on certain words case insensitive using regex (alteryx)-CodePudding

If my string is:

mary lamb The beast the castle THE large lake

I want to produce:

mary lamb
The beast
the castle
The large lake

If I do (?i)(?:(.*the)) then it only splits on the last the but i want to split on each "the" regardless of case.

CodePudding user response：

You need to use

(?i)(?=\bthe\b)
(?i)\s (?=\bthe\b)

See the regex demo. Details:

(?i) - case insensitive modifier
\s - one or more whitespaces
(?=\bthe\b) - a positive lookahead that matches a location immediately followed with the as a whole word.

CodePudding user response：

An alternative to RegEx:

Do a formula to replace "the" with CHR(13) "the"
Do a simpler Text to Columns, with \n for the delimiter and splitting to rows.