Parsing a String to get Numbers

[nabukhalaf]

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

 

[Tom Ogilvy]

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

 

[Guest]

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

 

[Myrna Larson]

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

 

[Tom Ogilvy]

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.