How to extract a number of characters before and after a substring in Excel-CodePudding

In Column A I have a large wall of text in each cell, and in Column B I want to see a snippet of information around the keyword "Climate Change" to better understand the context. For that, I want to only extract 100 characters before the keyword, and another 100 characters after the keyword.

I tried the usual left, right, find, mid functions but couldn't figure it out how to specify the number of characters i need

any help is appreciated, thanks

CodePudding user response：

=MID(A1,MAX(SEARCH("climate change",A1)-100,1),214)

The Max(x,1) check handles when there aren't 100 characters before climate search, because Mid returns an error when the starting position parameter is less than 1.

CodePudding user response：

You can use subtitute function.

=SUBSTITUTE(A2,"Climate Change","")

if you want to remove the extra spaces then use trim

=TRIM(SUBSTITUTE(A2,"Climate Change",""))