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

Question

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

Extract two words before and after the trageted word.xlsx

philipmannering · Answer

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')

mceleavey · Answer

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.

mceleavey solution.yxmd

philipmannering · Answer

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'), '_ ')