On Sat, 26 Jul 2008 04:31:22 GMT, "hardeep via OfficeKB.com"
<u44683@uwe wrote:

Dear sir
I have list of 15000 of Alphanumeric data for ex. ADEDO125ADSD589ADF121,
UIEIROIWS12556ERE545,ADAS15455212AD4564AD2
And so on. Now I want to extract number from this alphanumeric. Once I have
seen a formula of (SUBSITUTE) in a site it was so simple that any body can
use that formula. Its very short formula. Spouse a1:a1500 has alphanumeric
data put the formula in B1 and drag this formula to B7. In B7 You will find
the only number.
If you can help me in this matter
It Will Be most Appriciate

Regards
Hardeep


Here is another formula you may try:

=MID(SUMPRODUCT(-
-MID("01"&A1,SMALL((ROW(A1:A300)-1)*ISNUMBER(-MID("01"&A1,ROW(A1:A300),1)),ROW(A1:A300))+1,1),10 ^(300-ROW(A1:A300))),2,300)

This is an array formula and has to be confirmed with CTRL+SHIFT+ENTER
rather than just ENTER.

It has the following (known) limitations:

- The input string in cell A1 must be shorter than 300 characters

- There must be at most 15 digits in the input string.
(Following digits will be shown as zeroes.)

Maybe of no pratical use, but it will also handle the following two
cases correctly:

- a "0" as the first digit in the input will be shown correctly in the
output

- an input without any digits at all will give the empty string as
output (rather than 0).

Hope this helps / Lars-Åke