Excel: Finding adjacent words in one cell by using a common word in another cell

I have been grappling with this problem for some time. I have a list of statements, each of which is assigned to a cell (these are captured from another worksheet and stored in column B. In column C is a word that had been previously extracted from the statement. I want to capture the five words that are on either side of the captured word and list these words in column D. I tried using the MID function and setting the character length to 30, but this doesn't allow for those situations where 30 characters is too long or too short, such as at the end or start of a sentence. I need to capture whole words. I thought a regular expression may do the trick, but it would need to allow a variety of words in each cell of Column C. To illustrate, the following statement might appear in Column B: "There is little I can say about the state of the class. It is a poor example of the teacher's skill and I don't wish to attend anymore". The keyword in Column C is "class", and so I need to identify "about the state of the class. It is a poor example". Does that clarify my situation? Any assistance would be greatly appreciated. Many thanks in anticipation. Kilgore_Elder.

This question generated 11 answers. To proceed to the answers, click here.

This thread is current as of April 14, 2017.

For more resources for Microsoft Excel