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

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?

For a good start, such a macro.
Assumptions:
1. It only finds one phrase (keyword) in each line
2. Does not provide that the sentence begins with the searched phrase
3. Does not predict that there are less than 3 words in a sentence after the searched phrase.

Please Login or Register  to view this content.

Artik

For the version with Spill Functionality.

=IFERROR(GetMidSpil(A2,"covid test",1,3,1),"")
copy down
Where;
A2 has data
"covid test" = key string
1 = word(s) before key word
3 = word(s) after key words
1 = compre mathod, 0 for case sensitive, 1 for case insensitive

Please Login or Register  to view this content.

For all versions

=IFERROR(GetMid($A2,"covid test",1,3,COLUMN(A1),1),"")
Copy right + down
Where
COLUMN(A1) = index reference when multiple match found

Please Login or Register  to view this content.

Here's a formula solution, albeit a very complicated one. I'm sure someone here can simplify this a lot, but I piece-mealed it together and this is what I came up with.

Note that this is a little flexible and that it will work regardless of the phrase you use (doesn't have to be "covid test"). In this example, the phrase (or key words) is in cell B1 and the sentences are in cells B2:B4.

The long formula is this:

=MID(B2,SEARCH(CHAR(10),SUBSTITUTE(LEFT(B2,SEARCH($B$1,B2)-2)," ",CHAR(10),LEN(LEFT(B2,SEARCH($B$1,B2)-2))-LEN(SUBSTITUTE(LEFT(B2,SEARCH($B$1,B2)-2)," ",""))))+1,SEARCH($B$1,B2)-(SEARCH(CHAR(10),SUBSTITUTE(LEFT(B2,SEARCH($B$1,B2)-2)," ",CHAR(10),LEN(LEFT(B2,SEARCH($B$1,B2)-2))-LEN(SUBSTITUTE(LEFT(B2,SEARCH($B$1,B2)-2)," ",""))))+1)+LEN($B$1)+SEARCH(CHAR(10),SUBSTITUTE(RIGHT(B2,LEN(B2)-SEARCH($B$1,B2)-LEN($B$1))," ",CHAR(10),3)))

You say your version is "Microsoft Office" so I don't know if you mean MS365 or not, but if you do, using the LET formula helps to explain it:

Please Login or Register  to view this content.