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

Examples:
AA100.10
ANN242.21

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

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 he

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


--
Kind regards,

Niek Otten

"SHANNON" wrote in message
...
Examples:
AA100.10
ANN242.21

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

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

--
Regards,
Dave


"SHANNON" wrote:

Examples:
AA100.10
ANN242.21

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

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)


"SHANNON" wrote in message
...
Examples:
AA100.10
ANN242.21

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

Try...

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

Hope this helps!

In article ,
"SHANNON" wrote:

Examples:
AA100.10
ANN242.21

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

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


--
Regards,
Dave


"SHANNON" wrote:

Examples:
AA100.10
ANN242.21

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

Thanks, David!

--
Kind regards,

Niek Otten

"David Billigmeier" wrote in
message ...
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


--
Regards,
Dave


"SHANNON" wrote:

Examples:
AA100.10
ANN242.21

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

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

--
Kind regards,

Niek Otten


"David Billigmeier" wrote in
message ...
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


--
Regards,
Dave


"SHANNON" wrote:

Examples:
AA100.10
ANN242.21

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

On Thu, 1 Dec 2005 13:45:02 -0800, "SHANNON"
wrote:

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