On Wed, 27 Aug 2008 08:07:02 -0700, Bob Freeman
wrote:

Hello,

I am trying to pull out one string of text within a cell.

e.g. any string of text containing "gsm" from the following:

"One ream of paper at 670gsm" (which would return a value of either 670gsm
or ideally 670)

Not convinced that this is possible but any help gratefully received.

Many thanks,

Bob


This can be pretty easy to do using regular expressions, provided you can
define precisely what you want to do. I have assumed that, as with your
examples, there will be no <space between your searched for text string and
the remainder of what you wish to extract.

One way: Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/morefunc/english/index.htm

You can then use one of the Regex functions with an appropriate argument.

For example, to return the value of 670gsm:

A1: One ream of paper at 670gsm


Formula: =REGEX.MID(A1,"\S*gsm\S*",,FALSE)

to return just the 670 preceding the gsm:

=REGEX.MID(A1,"(\d*(?=gsm))",,FALSE)

---------------
or

A1: 5 abc 152XY30.5MM 670gsm (EKEE4)

To return all the characters in the substring containing XY:

=REGEX.MID(A1,"\S*xy\S*",,FALSE)

To return the numeric value preceding the MM:

=REGEX.MID(A1,"(?<=\D)\d*\.?\d+(?=mm)",,FALSE)

--ron