Getting rid of letters and leave only numbers??

[kelvin2088]

Hello experts!

I have thousands rows of data strings like following:

DR612491
AAL313490
7132134ALLI
AALC8214LAN
AD8123LKN8912
.....


I'm wondering if there's any function can help me trim those strings to only numbers? (getting rid of all letters, both in front, back and middle)

so that I can have output like following:
612491
313490
7132134
8214
81238912

Ahh...this is driving me crazy

 

[BobUmlas]

You can use this UDF:

Function Nos(Rg As Range)
Dim n As Integer
For i = Len(Rg) To 1 Step -1
If Asc(Mid(Rg.Value, i, 1)) < 58 And Asc(Mid(Rg.Value, i, 1)) > 47 Then
Nos = Mid(Rg.Value, i, 1) * 10 ^ n + Nos
n = n + 1
End If
Next
End Function

then enter =NOS(A1) for example...

 

[kelvin2088]

Thanks!!!!
I'll try.
Btw, is there anyway to avoid using VBA code? thx!

 

[pboltonchina]

ASAP Utilities will do this. Select Text, Advanced character removal and replacement, select None, then click the 1,2,3 button at the bottom, then Inverse and OK.

ASAP Utilities is a free add in for excel available to download from http://www.asap-utilities.com

 

[kelvin2088]

That sounds very promising!
I'll give it a try. (Not sure if i can install this add-on since everything's blocked on company computer )

ASAP Utilities will do this. Select Text, Advanced character removal and replacement, select None, then click the 1,2,3 button at the bottom, then Inverse and OK.

ASAP Utilities is a free add in for excel available to download from http://www.asap-utilities.com

 

[Haseeb Avarakkan]

Hello, see the below link.

http://www.xl-central.com/extract-numeric-characters.html

 

[kelvin2088]

OMG, this one is excellent and its exactly what I'm looking for!
Thank you!!

Hello, see the below link.

http://www.xl-central.com/extract-numeric-characters.html

 

[kelvin2088]

Thank you all for the help, what a beautiful community here!!