Extract numeric value from single cell

[Landyman]

Hello,

I have numeric values within a text string in a single cell. There i
no conformity to the length, or the make-up of the text.
There is only ever 1 numeric value (that may vary in length from 1-
digits long) within the text string.

I have used the find function, but is getting vary messy. Is there
numeric wildcard character I can use.

Im using Excel 2000

Any help would be much appreciate

 

[Peo Sjoblom]

This formula will "extract" a numeric value from a string

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM(--ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1))))

entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[icestationzbra]

here is another way, it is in VBA. it can be used like any othe
worksheet function, like, =GETNUM(A1).

put this code in a separate module, not in the worksheet's cod
module.

Public Function GetNum(varString As Variant)
Dim i As Long, n As Long, x As String, strTemp As String
n = Len(varString) 'length of the input string
If n < 1 Then Exit Function
strTemp = ""
For i = 1 To n
If IsNumeric(Mid(varString, i, 1)) Then
x = Mid(varString, i, 1) 'pick the byte if numeric
strTemp = strTemp & x 'append it to the string
End If
Next i
If Len(strTemp) = 0 Then
GetNum = "No digits found"
Else
GetNum = CDbl(strTemp) 'convert string to DOUBLE
End If
End Functio

 

[BenjieLop]

<< This formula will "extract" a numeric value from a string

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM(--ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1))))

entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum) >>

Hello Peo,

Your formula works very well and I am keeping it in my files because
have a similar application for it. I just need to ask you this ... wha
is the meaning of "1:100" in your formula?

I really need to understand it so I can make the required adjustment
when I do you formula in my application.

Thanks

 

[Dave Peterson]

I'm not Peo, but 100 is the limit that Peo placed on the length of the string in
A1.

If your strings are longer than 100, change all the 100's (1:100 twice and
100-sum once) to a number larger than the length of your string.

You could even modify Peo's formula slightly:

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),
LEN(A1)-SUM(--ISERROR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))

(still all one cell and still ctrl-shift-enter (array entered))

 

[BenjieLop]

Wow ... 100 for the length of string in a cell!!! I guess that's a very
long string.

Thank you for the explanation, Dave ... it is always helpful to
understand the terms in a formula in case some adjustments have to be
made later on. I will also keep your formula in my files (side by side
with Peo's). It is always nice to have more than 1 formula handy ...
makes me look like I know what I am doing!!!

Regards and once again, thanks a lot.


<< I'm not Peo, but 100 is the limit that Peo placed on the length of
the string in
A1.

If your strings are longer than 100, change all the 100's (1:100 twice
and
100-sum once) to a number larger than the length of your string.

You could even modify Peo's formula slightly:

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),
LEN(A1)-SUM(--ISERROR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))

(still all one cell and still ctrl-shift-enter (array entered)) >>

 

[Dave Peterson]

If you're using xl2002 or higher, put a small string in A1 (less than 10
characters) and then change all those 100's to 10.

Then select that cell with the formula and
Tools|formula auditing|evaluate formula
And you'll see how excel evaluates this very nice formula.