Extract Text to the Left and Right of a Delimiter such as _ (the underscore).

  • Thread starter Thread starter willik
  • Start date Start date
W

willik

Hello,

I am trying to extract text out of several cells to the right and left
of an underscore_character.

For example:

I want to extract this_text from this sentence or phase.

The result would look like this:

this_text

So, the formula would look for the underscore_ and then look for blank
spaces to the left and right of the underscore and delete all text
surrounding the blank spaces, including the spaces.

Any help would be appreciated.

Thank you,
 
Hi

This is pretty messy but should give you what you want.

I started off with the text
for something else this_text then something else
in A1

and the formula
=MID(A1,SEARCH("~~",SUBSTITUTE(A1," ","~",LEN(MID
(A1,1,SEARCH("_",A1,1)))-LEN(SUBSTITUTE(MID(A1,1,SEARCH
("_",A1,1))," ",""))),1)+1,SEARCH(" ",A1,SEARCH("_",A1,1))-
SEARCH("~~",SUBSTITUTE(A1," ","~",LEN(MID(A1,1,SEARCH
("_",A1,1)))-LEN(SUBSTITUTE(MID(A1,1,SEARCH
("_",A1,1))," ",""))),1)-1)

produced
this_text

Tony
 
Back
Top