Extract two words before and two words after the targeted word from string

[SagarGite]

Hello team,

I would like to have workflow for extracting two words before and two words after the targeted word from string.

String is in Column "Indication" and targeted word for the string is in column "Indication word". Expected output is in "Additional word info" column

Please see attached file for sample data and expected output.

Thanks in advance

[PhilipMannering]

Something like this kind of works,

[PhilipMannering]

You can also do it in one expression using the Formula Tool using,

trim(regex_replace('_ _ ' + [Indication] + ' _ _', '.*(\b\w+\b \b\w+\b ' + [Indication Word] + ' \b\w+\b \b\w+\b).*', '$1'), '_ ')

[mceleavey]

Hi @SagarGite ,

I used the method of splitting the data to rows on the space:

Then using a simple multi-row formula to say if the Indication word equals the Indication field, then take two above and below:

Then pivot it back with a cross-tab:

I hope this helps,

M.

[PhilipMannering]

Or you can do it with a single regex expression,

regex_replace([Indication], '.*?((\b\w+\b (\b\w+\b )?)?' + [Indication Word] + '( \b\w+\b( \b\w+\b)?)?).*', '$1')