Extract numbers from alphanumeric values

[tushar.tarafdar]

Thanks for the help!
Basically, what I'm looking for is i have a string value: 0few321make876many
I want to extract only numerical values from the string by excel formula only like 0321876.
Please help

[Admin]

Hi

try this

=TEXT(SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10),REPT("0",COUNT(--MID(A2,ROW($1:$25),1))))

I modified the formula which I got from here (http://www.mrexcel.com/forum/excel-questions/443983-extract-only-numbers-text-string.html#post2193684).

It's an array formula. Confirmed with CTRL + SHIFT + ENTER.

[Admin]

..and here is a non array formula

=TEXT(SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10),REPT("0",COUNT(INDEX(--MID(A2,ROW($1:$25),1),0,0))))

[Rick Rothstein]

The link Admin posted is too long for me to go through now, but I don't think the following was posted there (if it was, I am sorry for the repeat)...

Posted previously by Lars-Åke Aspelin (in an old newsgroup)...

=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),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 14 digits in the input string.
(Extra digits will be shown as zeroes.)

Maybe of no practical 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).