Extracting text around a string in Excel

If you need to extract text from an Excel cell which is X number of characters before and Y number of characters after a given search term, you can use the MID and MAX formulas with a nested SEARCH formula.

A formula in this format:

=MID(A2,MAX(SEARCH("stole a",A2)-35,1),70)

. . . can be set to return the number of characters before the search string equal to the value given in the MAX formula (in this example 35) and the number of characters from the start of the search string moving to the right equal to roughly half the value given for the MID formula (in this example 70).

. . . the SEARCH formula finds the position in the cell containing text where the first character of the search term appears.

The MAX formula then gets the value of the SEARCH result minus the number entered in the complete formula. In this it is case 35.

When the result from the MAX formula is fed into the MID formula, it is used to determine the position where the extraction should begin, and how many characters after that point it should include.