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

[Guest]

Examples:
AA100.10
ANN242.21

 

[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

 

[Guest]

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

 

[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)

 

[Domenic]

Try...

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

Hope this helps!

 

[Guest]

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

 

[Niek Otten]

Thanks, David!

--
Kind regards,

Niek Otten

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

 

[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

 

[Ron Rosenfeld]

Examples:
AA100.10
ANN242.21

1. Download and install Laurent Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

2. Then use this formula:

=REGEX.SUBSTITUTE(A1,"[^\d+\.]")


--ron