Identifying text within a cell
 

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

Identifying text within a cell
 

Can there be more than one occasion of numbers like in your example or can
there be strings like


"One ream of paper at 670gsm including 450 etc"

if the numbers right before gsm is the only numerical occurrence you can use


=--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),SEARCH("gsm",A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))


with your string in A1


--


Regards,


Peo Sjoblom

"Bob Freeman" wrote in message
...
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

Identifying text within a cell
 

Thank you Peo.

As you said, this works perfectly when there are no other numeric values in
the string and will be very useful.

You correctly predicted that there will be times where there will be several
other numbers in the string of text both before and after the string to be
looked at.

e.g.

5 xyz 152X30.5MM 670gsm (EKEE4)

Many thanks.



"Peo Sjoblom" wrote:

Can there be more than one occasion of numbers like in your example or can
there be strings like


"One ream of paper at 670gsm including 450 etc"

if the numbers right before gsm is the only numerical occurrence you can use


=--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),SEARCH("gsm",A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))


with your string in A1


--


Regards,


Peo Sjoblom

"Bob Freeman" wrote in message
...
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

Identifying text within a cell
 

=--TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),FIND("gsm",SUBSTITUTE(A1,"
",REPT(" ",99)))-99,99))


"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

Identifying text within a cell
 

My formua is more plexible and it doesn't depend on any other numeric values
in the string


"Bob Freeman" wrote:

Thank you Peo.

As you said, this works perfectly when there are no other numeric values in
the string and will be very useful.

You correctly predicted that there will be times where there will be several
other numbers in the string of text both before and after the string to be
looked at.

e.g.

5 xyz 152X30.5MM 670gsm (EKEE4)

Many thanks.



"Peo Sjoblom" wrote:

Can there be more than one occasion of numbers like in your example or can
there be strings like


"One ream of paper at 670gsm including 450 etc"

if the numbers right before gsm is the only numerical occurrence you can use


=--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),SEARCH("gsm",A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))


with your string in A1


--


Regards,


Peo Sjoblom

"Bob Freeman" wrote in message
...
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

Identifying text within a cell
 

Why is it more flexible, it all depends on what the rules are.

--


Regards,


Peo Sjoblom

"Teethless mama" wrote in message
...
My formua is more plexible and it doesn't depend on any other numeric
values
in the string


"Bob Freeman" wrote:

Thank you Peo.

As you said, this works perfectly when there are no other numeric values
in
the string and will be very useful.

You correctly predicted that there will be times where there will be
several
other numbers in the string of text both before and after the string to
be
looked at.

e.g.

5 xyz 152X30.5MM 670gsm (EKEE4)

Many thanks.



"Peo Sjoblom" wrote:

Can there be more than one occasion of numbers like in your example or
can
there be strings like


"One ream of paper at 670gsm including 450 etc"

if the numbers right before gsm is the only numerical occurrence you
can use


=--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),SEARCH("gsm",A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))


with your string in A1


--


Regards,


Peo Sjoblom

"Bob Freeman" wrote in message
...
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

Identifying text within a cell
 

This works very well thank you and will save me a lot of time.

I have tried to follow the code to adjust for the scenario whereby I could
return the full string of text containing a designated character/ string of
characters (which I recognise would have to be unique, e.g. XY from the
string below), but without success:

e.g. 5 abc 152XY30.5MM 670gsm (EKEE4) would return 152XY30.5MM

If you were able to help that would be great.

Many thanks


"Teethless mama" wrote:

=--TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),FIND("gsm",SUBSTITUTE(A1,"
",REPT(" ",99)))-99,99))


"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

Identifying text within a cell
 

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

Identifying text within a cell
 

Thank you - I have downloaded the excel add in and am successfully using the
new formulas.


"Ron Rosenfeld" wrote:

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

Identifying text within a cell
 

On Thu, 28 Aug 2008 09:30:06 -0700, Bob Freeman
wrote:

Thank you - I have downloaded the excel add in and am successfully using the
new formulas.

Glad to help. Thanks for the feedback.

One limitation which, hopefully, will not apply to you is that the strings
cannot be more than 255 characters in length.

If that is an issue, we can write a VBA add-in which will accomplish the same
thing, but the regex pattern will need to be a bit different.
--ron