finding text strings

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

Toddman

Can anyone tell me how to find and pull out text looking like this:

<base>_<more>


The <base> and <more> are strings of varying lengths. All searche
strings have more an underscore.

And what about if there are multiple strings?

Thanks for any help
 
Have you tried this?

Edit-Find
Type *_*
Find All
Ctrl-A
Edit-Copy
Close the Dialog Box
Insert-Worksheet
Ctrl-
 
I appreciate the feedback but it doesn't accomplish what I want to do.
Actually my explanation before was lacking. Here is a bette
explanation of need:

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



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
 
I believe this will do what you are wanting...


=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND("_",A1)-FIND(" ",A1)))

where A1 is the text you are searching.

Let me know if this helps
 
To: Toddman

This is a formula that worked for me. The only difference is this onl
identifies the first instance of the underscore. So you might be abl
to modify this formula to extract instances of the underscore. Thi
formula was given to me by "acw" on the following thread . . .

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

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

P.S. Make sure there are not any carriage returns in the formula. Thi
could generate an error.

Regards,
Kenneth C. ( "willik"
 
Back
Top