isolating numbers in a text string

[WildAngus]

Hi.

If I have a string including Alphas and Numerics, is there any way of pulling out just the numerics.
e.g. Cell A1 = "A78SD9F8G" and I want cell B1 to return just "7898" (ie removing all the Aphas)

Is there a quick formula that can do this?

Any help would be appreciated.

Thanks in advance.

 

[VoG]

Try Extracting numbers from alphanumeric strings - Excel - Office.com

 

[Firefly2012]

Since you have mutliple occurrences of digits you could do worse than check out Ron Coderre's formula here:
http://www.mrexcel.com/forum/excel-questions/443983-extract-only-numbers-text-string.html

 

[WildAngus]

Since you have mutliple occurrences of digits you could do worse than check out Ron Coderre's formula here:
http://www.mrexcel.com/forum/excel-questions/443983-extract-only-numbers-text-string.html

Excellent - works a treat - Thank you.

 

[Rick Rothstein]

Posted previously by Lars-Åke Aspelin...

=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. (Following 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).