search and extract word based on presence of underscore

  • Thread starter Thread starter Toddman
  • Start date Start date
T

Toddman

Hello,

I am trying to extract text out of several cells to the right and lef
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 blan
spaces to the left and right of the underscore and delete all tex
surrounding the blank spaces, including the spaces.

Any help would be appreciated.

Thank you
 
One way:

=MID(LEFT(A1,FIND(" ",A1, FIND("_",A1))-1),FIND("$",SUBSTITUTE(LEFT(A1,
FIND(" ",A1,FIND("_",A1))-1)," ","$",LEN(LEFT(A1, FIND(" ",A1,
FIND("_",A1))-1))-LEN(SUBSTITUTE(LEFT(A1,FIND(" ",A1, FIND("_",A1))-1),
" ",""))))+1,255)
 
Back
Top