# Extract Numbers from an alphanumeric string when numbers are not clustered

#### Shweta

##### Well-known Member
Hi,

Col A

 RED458ELT9 s324rake8ete drk77dFTT563j FKD5RR 4tryr351 3fdffd4f5 Rkknk4656s 9c5vcv933`90923]] À87&‰ð\$ 45§§Ø7çz24[ ~9¯œ»~ô855‹eg87Û©37

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>
Col B(Output)

 4589 3248 77563 5 4351 345 4656 9593390923 8745724 98558737

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

Tried many formulas those generally work. But noone is working on the above mentioned data.

Any help would be appreciated.

Regards,
Shweta

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
The only way I can think of doing this is with a User-Defined function...

Code:
``````Function Numbers(c As Range)
Dim i As Long
Dim x As String

For i = 1 To Len(c)
x = Mid(c, i, 1)
If x Like "#" Then Numbers = Numbers & x
Next i

End Function``````
Sheet1

 * A B 1 RED458ELT9 4589 2 s324rake8ete 3248 3 drk77dFTT563j 77563 4 FKD5RR 5 5 4tryr351 4351 6 3fdffd4f5 345 7 Rkknk4656s 4656 8 9c5vcv933`90923]] 9593390923 9 À87&‰ð\$ 45§§Ø7çz24[ 8745724 10 ~9¯œ»~ô855‹eg87Û©37 98558737

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:177px;"><col style="width:77px;"></colgroup><tbody>
</tbody>

 Cell Formula B1 =numbers(A1) B2 =numbers(A2) B3 =numbers(A3) B4 =numbers(A4) B5 =numbers(A5) B6 =numbers(A6) B7 =numbers(A7) B8 =numbers(A8) B9 =numbers(A9) B10 =numbers(A10)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

try this array formula
Code:
``=SUM(MID(0&A1,LARGE(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1))``
i got from here
which probably came from here or excel forum

Excel Workbook
AB
1RED458ELT94589
2s324rake8ete3248
3drk77dFTT563j77563
4FKD5RR5
54tryr3514351
63fdffd4f5345
7Rkknk4656s4656
89c5vcv933`90923]]9593390923
987&\$ 457z24[8745724
10~9~855eg873798558737
Sheet1

Last edited:
Thanks both of you.

I knew the VBA code for it but I wanted the answer with excel. Sorry I forgot to mention it.

Thanks Martindwilson! your formula is working fine.

Regards,
Shweta

Hi Shweta

Up to 15 digits try also:

=NPV(-0.9,,IFERROR(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1)%,""))

confirmed with Ctrl-Shift-Enter

Format the cells as General or Number

Thanks pgc01!

Great Formula! Can u please explain me how is it working. I don't have much knowledge about the function "NPA".

And why up to 15 digits only?

Regards,
Shweta

Thanks pgc01!

Great Formula! Can u please explain me how is it working. I don't have much knowledge about the function "NPA".

And why up to 15 digits only?

Regards,
Shweta

A number in a cell has a maximum of 15 digits.
Any formula that returns a number has that limit, for ex. the formula posted earlier by Martin.
If you need more digits the result cannot be a number, it must be a string, like in Neil's solution.

An explanation of the formula:

HTH

A number in a cell has a maximum of 15 digits.

Sorry, I meant 15 significant digits.

Thank you pgc01!

You're welcome. Thanks for the feedback.

1,196,288
Messages
6,014,500
Members
441,825
Latest member

### 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