Extract Numbers from an alphanumeric string when numbers are not clustered

Shweta

Well-known Member
Joined
Jun 5, 2011
Messages
514
Hi,

Please help me out on the below query.

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.

Please help me out with an appropriate formula

Any help would be appreciated.

Thanks in advance!

Regards,
Shweta



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

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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

*AB
1RED458ELT94589
2s324rake8ete3248
3drk77dFTT563j77563
4FKD5RR5
54tryr3514351
63fdffd4f5345
7Rkknk4656s4656
89c5vcv933`90923]]9593390923
9À87&‰ð$ 45§§Ø7çz24[8745724
10~9¯œ»~ô855‹eg87Û©3798558737

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

Spreadsheet Formulas
CellFormula
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
 
Upvote 0
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
http://www.youtube.com/watch?v=bqsvOygpQWc
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:

http://www.mrexcel.com/forum/showthread.php?628855-Parse-Numerics&p=3121144

HTH
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top