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

[Kilgore_elder]

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.

 

[footoo]

I understand the amendment to "For c = 2 To 30 Step 3", changing the 3 to 4, but this line, rng.Offset(0, 2) = "=CenterKey(RC[-2],RC[-1], 5)", Is not present in the macro. Do i simply add this after,
Set rng = ws.Range(ws.Cells(2, c), ws.Cells(Rows.Count, c).End(xlUp))?

Yes, the last macro I posted is complete.
You need to adjust the line in question to fit your layout.
Presently, the macro enters the UDF first in col D (rng.Offset(0,2)) and then in the other columns (For c = 2 to 30 Step ?) - Note: check whether 30 is the correct number.
Adjust the CenterKey col references to fit what you need. At present they are as follows:
C[-2] Two columns before the rng column, i.e. col B in the case of the entry in col D
C[-1] One column before the rng column, i.e. col C in the case of the entry in col D

Just out of interest, why do you need to do this?

 

[Kilgore_elder]

Ah thanks footoo. I had not seen mikerickson's posting before your amendments to your macro. Hence my confusion. To satisfy your curiosity, i am using this workbook to analyse student sentiment in their evaluation comments at a university. There is nothing available that does this, so I decided to build my own. This aspect (as well as one other thing) was making it inefficient, but now that should be solved.

 

[Kilgore_elder]

Thanks mikerickson. For some reason I didn't see your post. I'll give it a try along with that of footoo.

 

[mikerickson]

footoo has incorporated my UDF into his most recent solution.

 

[Kilgore_elder]

Yes, thank you for that. I appreciate your assistance with this.