SELECT one, two, three, n words before and after a keyword in a string

[bharath99]

I have data in an excel sheet as below:

id Text
------------
1 There is a need to go for a PCR covid test if you have symptoms
2 No need to go for a covid test if you don't have symptoms
3 government increased test centres to conduct covid test to decrease the waiting time.

Question: I need to search for 'covid test'(keyword: 'covid test') in each row and fetch the data as below:



Result
----------------
PCR covid test if you have
a covid test if you don't
conduct covid test to decrease the


Logic: Need to search for the string 'covid test' in each row and display one word before 'covid test' and three words after 'covid test'.

I'm not sure on making up the formula. Can any one please suggest?

[FDibbins]

Administrative Note:

Welcome to the forum.

Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here.

Please see Forum Rule #5 about thread duplication.

I am closing this thread, but you may continue here in the original thread: link to be added here

https://www.excelforum.com/excel-pro...ml#post5623575