Return words before and after a keyword in excel

**boriherter** · 01-09-2013, 09:07 PM

Hi Everyone,

I'm working on statistics for my local baseball team, and I would like to be able to output words before and after a key word in excel.

When given data from each game, I get the following string as an example:

Pitching Change: Paul McCartney replaces John Lennon.

I would like to extract the players names ie. The first two words before the word "replaces" and the first two words after the word "replaces".

So my desired return will be "Paul McCartney" in one cell and "John Lennon" in another.

NOTE:
- I tried a formula that returns the 3rd, 4th, 6th and 7th words but it does not work as sometimes players have three or four words in their names and this messes me up. This is why I need it based around the keyword "replaces". I hope this makes sense, I've tried to explain it better in the attached sheet!
- If possible, I need the fullstop removed from the results. ie I would like "John Lennon" returned instead of "John Lennon."

Thanks in advance

Tom

**Haseeb Avarakkan** · 01-09-2013, 09:22 PM

Try:

B2,

=MID(LEFT(A2,SEARCH("replaces",A2)-2),FIND(":",A2)+2,250)

C2,

=MID(A2,SEARCH("replaces",A2)+9,LEN(A2)-SEARCH("replaces",A2)-8-(RIGHT(A2)="."))

Copy down.