if anyone can provide me with a function that can parse a string t
retrieve numbers, numbers including decimals etc that would b
appreciated. I.E. "This is my string 0.000

--
nabukhala
-----------------------------------------------------------------------
nabukhalaf's Profile: http://www.excelforum.com/member.php...fo&userid=1504
View this thread: http://www.excelforum.com/showthread.php?threadid=26658

Already did -- but here it is again.

Sub Tester5()
Dim sString As String, sStr As String
Dim i As Long, sChr As String
sString = "This is my string 0.000"
For i = 1 To Len(sString)
sChr = Mid(sString, i, 1)
If IsNumeric(sChr) Or sChr = "." Then
sStr = sStr & sChr
End If
Next
MsgBox sStr

End Sub

gave me 0.000

--
Regards,
Tom Ogilvy


"nabukhalaf" wrote in message
...

if anyone can provide me with a function that can parse a string to
retrieve numbers, numbers including decimals etc that would be
appreciated. I.E. "This is my string 0.000"


--
nabukhalaf
------------------------------------------------------------------------
nabukhalaf's Profile:
http://www.excelforum.com/member.php...o&userid=15040
View this thread: http://www.excelforum.com/showthread...hreadid=266583

Hi

I've rewritten Tom's Sub as a function

Function MyNum(sString As String) As Double
Dim sStr As String
Dim i As Long, sChr As String

For i = 1 To Len(sString)
sChr = Mid(sString, i, 1)
If IsNumeric(sChr) Or sChr = "." Then
sStr = sStr & sChr
End If
Next
MyNum = sStr
End Function

Copy this into a VB Module, then type +mynum(cellref)and
copy down

Regards
Peter
-----Original Message-----
Already did -- but here it is again.

Sub Tester5()
Dim sString As String, sStr As String
Dim i As Long, sChr As String
sString = "This is my string 0.000"
For i = 1 To Len(sString)
sChr = Mid(sString, i, 1)
If IsNumeric(sChr) Or sChr = "." Then
sStr = sStr & sChr
End If
Next
MsgBox sStr

End Sub

gave me 0.000

--
Regards,
Tom Ogilvy


"nabukhalaf"
wrote in message
...

if anyone can provide me with a function that can parse
a string to
retrieve numbers, numbers including decimals etc that
would be
appreciated. I.E. "This is my string 0.000"


--
nabukhalaf
--------------------------------------------------------
----------------
nabukhalaf's Profile:
http://www.excelforum.com/member.php?
action=getinfo&userid=15040
View this thread:
http://www.excelforum.com/showthread...hreadid=266583



.

Do you suppose one needs to be concerned with a string like

5 apples and 6 oranges

Your code would give 56, which isn't in the original string.

To eliminate that, you would have to set a flag that exits the loop when you
hit a non-number.

Sub Tester5()
Dim sString As String, sStr As String
Dim i As Long, sChr As String
Dim HaveDigits As Boolean

sString = "This is my string 0.000"
HaveDigits = False
For i = 1 To Len(sString)
sChr = Mid(sString, i, 1)
If IsNumeric(sChr) Or sChr = "." Then
sStr = sStr & sChr
HaveDigits = True
ElseIf HaveDigits Then
Exit For
End If
Next
MsgBox sStr
End Sub

On Tue, 5 Oct 2004 15:16:43 -0400, "Tom Ogilvy" wrote:

Already did -- but here it is again.

Sub Tester5()
Dim sString As String, sStr As String
Dim i As Long, sChr As String
sString = "This is my string 0.000"
For i = 1 To Len(sString)
sChr = Mid(sString, i, 1)
If IsNumeric(sChr) Or sChr = "." Then
sStr = sStr & sChr
End If
Next
MsgBox sStr

End Sub

gave me 0.000

Do you suppose one needs to be concerned with a string like
If your seriously asking a question, I suppose we could imagine all kinds of
situations, but the example provided was a single number embedded in a
string; so while I considered the problem of which you speak, I didn't
address it. Even if two or more number sequences were possible, the OP gave
no indication which should be extracted - however, your modification
certainly presents an approach to dealing with the problem.

--
Regards,
Tom Ogilvy




"Myrna Larson" wrote in message
...
Do you suppose one needs to be concerned with a string like

5 apples and 6 oranges

Your code would give 56, which isn't in the original string.

To eliminate that, you would have to set a flag that exits the loop when
you
hit a non-number.

Sub Tester5()
Dim sString As String, sStr As String
Dim i As Long, sChr As String
Dim HaveDigits As Boolean

sString = "This is my string 0.000"
HaveDigits = False
For i = 1 To Len(sString)
sChr = Mid(sString, i, 1)
If IsNumeric(sChr) Or sChr = "." Then
sStr = sStr & sChr
HaveDigits = True
ElseIf HaveDigits Then
Exit For
End If
Next
MsgBox sStr
End Sub

On Tue, 5 Oct 2004 15:16:43 -0400, "Tom Ogilvy" wrote:

Already did -- but here it is again.

Sub Tester5()
Dim sString As String, sStr As String
Dim i As Long, sChr As String
sString = "This is my string 0.000"
For i = 1 To Len(sString)
sChr = Mid(sString, i, 1)
If IsNumeric(sChr) Or sChr = "." Then
sStr = sStr & sChr
End If
Next
MsgBox sStr

End Sub

gave me 0.000