Search code examples
excelexcel-formulasubstringextract

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

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

Solution

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