Excel - Get the Word to the Left or Right of Another Word (text string)

I am trying to figure out how to get the WHOLE word to the left or right of an anchor term using a formula.

For example:

IF anchor term = 'excel formula'

Word to left or right.PNG

Here is a sample workbook:
Word to Left or Right Formula.xlsx

I'm sure this is a simple len formula but I run into issues if the word has white space in the front and back or doesn't.

Thanks for the help. The formula is not quite right. I only need the word to the immediate left or right of the focus word.

Example: if the phrase is "buy new home siding today now"

I don't need:
Left - "buy new home"
Right - "today now"

I do need:
Left - "home"
Right - "today"

Example Output.xlsx

Going back to my original question, I am trying to figure out how to get the WHOLE word to the left or right of an anchor term using a formula.

Said another way, I want to get the immediate word to the left or right of another word in a phrase.

Your formulas get the beginning word and the ending word of a phrase. That's not what I'm trying to do.

I am trying to get the immediate word to the left and right of an ANCHOR term.

Another example.xlsx

In your example:
"chicago roof companies"

Why not chicago (left) and companies (right).

Answer: Yes, that would be correct but only since this is a 3-word phrase.

Let's say it was a 4-word phrase:
"chicago IL roof companies"

The LEFT word I want is "IL" and not "chicago" or "chicago IL". I just want the immediate word to the left of "roof" which would be "IL".

for left column
=TRIM(RIGHT(SUBSTITUTE(" "&TRIM(LEFT($A2,FIND("excel formula",$A2)-1))," ",REPT(" ",LEN($A2))),LEN($A2)))

for right column
=TRIM(LEFT(SUBSTITUTE(MID($A2,FIND("excel formula",$A2)+LEN("excel formula")+1,LEN($A2))," ",REPT(" ",LEN($A2))),LEN($A2)))

martindwilson - thanks but when I open your solution, the "right word" formula has a #NAME? error.

Ghozi Alkatiri - Thank you! Your solution solves finding the immediate word to the left or right of a certain word within a phrase.

Thanks to you both for offering your time and knowledge. Rep Given.