Identifying text within a cell

[Bob Freeman]

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

 

[Peo Sjoblom]

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&"0123456789")),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]

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.

 

[Teethless mama]

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

 

[Teethless mama]

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

 

[Peo Sjoblom]

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

--


Regards,


Peo Sjoblom

 

[Bob Freeman]

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

 

[Ron Rosenfeld]

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

 

[Bob Freeman]

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

 

[Ron Rosenfeld]

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