On Tue, 15 Dec 2009 06:39:18 -0800, Waheed Ajouhaar wrote:

A very good day to all,
I am looking for a formula that would remove all non-numeric data from cells and return only a number. PS- notice that there is one numeric character that also needs to be removed - the number after the "m". also note that the formula must be able to handle each variation of text below.

Data:

A1 = "Gaz = 3 421 786 m3"
A2 = "Gaz=3 421 786 m4"
A3 = "Gaz=3421786m5"
A4 = "Gaz = 3,421,786 m6"
A5 = "Gaz=3,421,786m7"
A6 = "Gaz =3 421 786 m3"

Result:
The resultant for each line should be "3421786"

Looking forward to see the result.
Thanks :-)


Submitted via EggHeadCafe - Software Developer Portal of Choice
Dynamic ASP.NET charts with Office Web Components
http://www.eggheadcafe.com/tutorials...charts-wi.aspx

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