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

user_8391034 0
Viewed 44

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

extract substring excel-formula excel
2 Answers

=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.

user_74445070

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",""))

Example

user_104322160
Related