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

Question

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.

@skara9 - That ignores the first "the" and splits after "the" for the others — BobsBugs, Jan 25 '22 at 16:29

score 0 · Answer 1 · answered Jan 25 '22 at 16:29

0

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.

answered Jan 25 '22 at 16:29

Wiktor Stribiżew

1

Perfect, thank you – BobsBugs Jan 25 '22 at 16:34
Problem is, this is invalid in Alteryx as it complains of an empty expression if i try to parse it. If I tokenize it, the fields are empty. Works in your example mind! – BobsBugs Jan 25 '22 at 17:33

score 0 · Answer 2 · answered Jan 26 '22 at 05:46

0

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.

answered Jan 26 '22 at 05:46

johnjps111

2 Answers2