How do I extract numbers from a cell with both text and numbers?

N

Niek Otten

You could use this User defined Function:

Function StripTxt(a As String) As String
' Strips all non-numeric characters from a string
' Returns a string, not a number!
Dim i As Long
Dim b As String
For i = 1 To Len(a)
b = Mid$(a, i, 1)
If Asc(b) > 47 And Asc(b) < 58 Then StripTxt = StripTxt + b
Next i
End Function

If you're new to VBA, look here:

http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
D

David Billigmeier

Assuming all of your values start with an unknown number of alpha characters
followed by a number (i.e. there are no values like AA100.10AB in which you
have alpha-numeric-alpha), this formula will work. This formula uses A1 as
the referenece, change to fit your data. Enter this using CTRL+SHIFT+ENTER
as it is an array function:

=RIGHT(A1,LEN(A1)-MATCH(FALSE,ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))))
 
B

Bob Phillips

Hi Shannon,

Here is a solution that Domenic posted less than an hour ago

=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"
0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Domenic

Try...

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),1024)+0

Hope this helps!
 
D

David Billigmeier

Niek's formula will work but it will also strip off any decimal points in
your number. Change to the following if you want to keep the decimal point
included.

Function StripTxt(a As String) As String
' Strips all non-numeric characters from a string
' Returns a string, not a number!
Dim i As Long
Dim b As String
For i = 1 To Len(a)
b = Mid$(a, i, 1)
If ((Asc(b) > 47 And Asc(b) < 58) Or Asc(b) = 46) Then StripTxt =
StripTxt + b
Next i
End Function
 
N

Niek Otten

Thanks, David!

--
Kind regards,

Niek Otten

David Billigmeier said:
Niek's formula will work but it will also strip off any decimal points in
your number. Change to the following if you want to keep the decimal
point
included.

Function StripTxt(a As String) As String
' Strips all non-numeric characters from a string
' Returns a string, not a number!
Dim i As Long
Dim b As String
For i = 1 To Len(a)
b = Mid$(a, i, 1)
If ((Asc(b) > 47 And Asc(b) < 58) Or Asc(b) = 46) Then StripTxt =
StripTxt + b
Next i
End Function
Click to expand...
 
N

Niek Otten

I changed that so it works for my Dutch friends as well:

Function StripTxt(a As String) As String
' Strips all non-numeric characters from a string
' Returns a string, not a number!
Dim i As Long
Dim b As String
For i = 1 To Len(a)
b = Mid$(a, i, 1)
If ((Asc(b) > 47 And Asc(b) < 58) Or b = Application.DecimalSeparator)
Then StripTxt = StripTxt + b
Next i
End Function

But now I feel I should add it as an option, and also the option for string
or number result
 
Top