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

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 here:

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


--
Kind regards,

Niek Otten

"SHANNON" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> Examples:
> AA100.10
> ANN242.21

Try...

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

Hope this helps!

In article <[email protected]>,
"SHANNON" <[email protected]> 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" <[email protected]> wrote in
message news:[email protected]...
> 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" <[email protected]> wrote in
message news:[email protected]...
> 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"
<[email protected]> 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