Parsing a string to retrieve numerals

[nabukhalaf]

Hi, I'm fairly new to programming macro's if anyone can provide me wit
a function that can parse a string to retrieve numbers, number
including decimals etc that would be appreciated. I.E. "This is m
string 0.000"

I am currently using the GetWord Function, if anyone can revise th
GetWord to GetNum or if you have a function of your own. Thanks!

Function SF_getWord(ByVal Haystack As String, ByVal WordNumber As Long
As String
'return nth word of a string
'if the string is empty, a zero-length string is returned
'if there is only one word, the initial string is returned
'if wordnumber is 0 or negative, a zero-length string is returned
'if wordnumber is larger than the number of words in the string,
zero-length string is returned
'SF_getWord(" This is my string ",2) returns "is"
Dim i, lngWords As Long
Haystack = SF_unSpace(Haystack)
If SF_isNothing(Haystack) Then
SF_getWord = Haystack
Else
If WordNumber > 0 Then
lngWords = SF_countWords(Haystack)
If WordNumber > lngWords Then
Haystack = ""
Else
If lngWords > 1 Then
'cut words at the left
For i = 1 To WordNumber - 1
Haystack = Mid(Haystack, InStr(Haystack, Blank
+ 1)
Next i
'cut words at the right, if any
i = InStr(Haystack, Blank)
If i > 0 Then Haystack = Left(Haystack, i - 1)
End If
End If
Else
Haystack = ""
End If
SF_getWord = Haystack
End If
End Functio

 

[Tom Ogilvy]

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

 

[Myrna Larson]

This function will work if the words in the text are separated by just spaces.

Function FirstNumber(ByVal Haystack As String) As Variant
Dim i As Long
Dim sTemp As String
Dim Words() As String

FirstNumber = cvErr(xlErrValue)
If Len(HayStack) = 0 Then Exit Function

sTemp = Application.Trim(Haystack)
sTemp = Replace(sTemp, ", ", " ")

'or, to remove all commas
'sTemp = Replace(sTemp, ",", "")

Words() = Split(sTemp, " ")

For i = LBound(Words) To UBound(Words)
If IsNumeric(Words(i)) Then
FirstNumber = CDbl(Words(i))
Exit Function
End If
Next i

End Function