Your formuala also seems much simpler than mine.
in [General]
|
Prev: How do I round up numbers when dividing?
Next: Can I protect all excel tabs in a file with one password entry
From: Waheed on 17 Dec 2009 06:07 Hi Ron,Your formuala also seems much simpler than mine. I came up with the following formula before I saw your solution: =(REPLACE(REPLACE(SUBSTITUTE(SUBSTITUTE(A1," ",""),",",""),SEARCH("Gaz=",SUBSTITUTE(SUBSTITUTE(A1," ",""),",","")),4,""),SEARCH("m3",REPLACE(SUBSTITUTE(SUBSTITUTE(A1," ",""),",",""),SEARCH("Gaz=",SUBSTITUTE(SUBSTITUTE(A1," ",""),",","")),4,"")),2,""))*1 Many thanks! Waheed Ron Rosenfeld wrote: If your strings are exactly like the above, where the number starts with 15-Dec-09 If your strings are exactly like the above, where the number starts with the first non-space character after the "=", and the first non-space character after the number will always be an "m" (and that is the only "m" in the string), then you could use this formula: =--MID(SUBSTITUTE(LEFT(A1,SEARCH("m",A1)-1)," ",""), FIND("=",SUBSTITUTE(LEFT(A1,SEARCH("m?",A1)-1)," ",""))+1,99) --ron Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice XML/XPath Query On Attributes http://www.eggheadcafe.com/tutorials/aspnet/afcc434a-9125-4fc8-93c6-580045c565bf/xmlxpath-query-on-attrib.aspx
From: Ron Rosenfeld on 17 Dec 2009 08:33
On Thu, 17 Dec 2009 03:07:52 -0800, Waheed Ajouhaar wrote: >Hi Ron,Your formuala also seems much simpler than mine. >I came up with the following formula before I saw your solution: > >=(REPLACE(REPLACE(SUBSTITUTE(SUBSTITUTE(A1," ",""),",",""),SEARCH("Gaz=",SUBSTITUTE(SUBSTITUTE(A1," ",""),",","")),4,""),SEARCH("m3",REPLACE(SUBSTITUTE(SUBSTITUTE(A1," ",""),",",""),SEARCH("Gaz=",SUBSTITUTE(SUBSTITUTE(A1," ",""),",","")),4,"")),2,""))*1 > >Many thanks! >Waheed That's because of the assumptions I made (see below). So I only needed to replace <space>'s between the "=" and the "m" with nothing. > > > >Ron Rosenfeld wrote: > >If your strings are exactly like the above, where the number starts with >15-Dec-09 > >If your strings are exactly like the above, where the number starts with the >first non-space character after the "=", and the first non-space character >after the number will always be an "m" (and that is the only "m" in the >string), then you could use this formula: > >=--MID(SUBSTITUTE(LEFT(A1,SEARCH("m",A1)-1)," ",""), >FIND("=",SUBSTITUTE(LEFT(A1,SEARCH("m?",A1)-1)," ",""))+1,99) >--ron > >Previous Posts In This Thread: > > >Submitted via EggHeadCafe - Software Developer Portal of Choice >XML/XPath Query On Attributes >http://www.eggheadcafe.com/tutorials/aspnet/afcc434a-9125-4fc8-93c6-580045c565bf/xmlxpath-query-on-attrib.aspx --ron |