Parsing strings for numbers

[Dudely]

I have a string I pluck from a website. That string may or may not
contain a dollar figure such as $400000.

I would like to determine if in fact it does contain such a number
whether or not that number is greater than say $300000,

If it's less than that number, or contains no dollar figures at all, I
store it in an Excel cell otherwise I discard the string and go to the
next string and repeat.

I'm not sure where to start. Thoughts that come to mind include
inStr(), filter() and find(), but I don't quite understand how to use
filter(), and the other two don't seem quite right for the job.

As I type this out, I'm thinking that first I have to determine if the
string has a number - any number at all. Not sure how to do that.
Perhaps split() the string into a multi-element array and then check
each array element to see if it's a number with... isNum?

Better, simpler ideas? Actual code?

Thank you

 

[krazymike]

You can use RegExp.

Add a reference to Microsoft VBScript Regular Expressions 5.5

Try this code:

Dim regEx As RegExp, sTest As String
Sub a()
Set regEx = New RegExp
regEx.Pattern = "[0123456789]+"
regEx.IgnoreCase = True
Dim v As Variant
v = isNum("A LOT OF LETTERS AND A FEW NUMBERS $987654321")
If IsNull(v) Then
MsgBox "No Numbers"
Else
MsgBox CStr(v)
End If
End Sub

Function isNum(sTemp As String) As Variant
If regEx.Test(sTemp) Then sTest = regEx.Execute(sTemp)(0)
If IsNumeric(sTest) Then
isNum = sTest
Else
isNum = Null
End If
End Function

 

[JP]

If you are doing this in the worksheet:

=ISNUMBER(1*MID(A1,ROW($1:$9),1))

will return TRUE if there is a number in the string in A1. Adjust
"$1:$9" as appropriate for your typical string length. For example, if
the string is usually 10 characters, change it to
"=ISNUMBER(1*MID(A1,ROW($1:$10),1))".

This is an array formula and must be committed to the worksheet by
pressing Ctrl-Shift-Enter.

(from http://office.microsoft.com/en-us/excel/HA011549011033.aspx)

HTH,
JP