Removing various combinations of text and returning only a number inexcel
 

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

Removing various combinations of text and returning only a number in excel
 

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

Your formuala also seems much simpler than mine.
 

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(SUBSTITUT E(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...on-attrib.aspx

Your formuala also seems much simpler than mine.
 

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(SUBSTITUT E(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...on-attrib.aspx
--ron