# isolating numbers in a text string

#### WildAngus

##### New Member
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.

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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).

Replies
6
Views
553
Replies
9
Views
305
Replies
5
Views
258
Replies
3
Views
259
Replies
3
Views
187

1,206,830
Messages
6,075,113
Members
446,123
Latest member
junkyardforme

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back