Examples:
AA100.10
ANN242.21

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

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

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

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

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

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

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

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