Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identifying cells without certain text strings | Excel Worksheet Functions | |||
Identifying text to split into columns | Excel Worksheet Functions | |||
Identifying Current Cell | Excel Worksheet Functions | |||
Identifying a cell/value in a table | Excel Discussion (Misc queries) | |||
Identifying cells with similar text | Excel Discussion (Misc queries) |