I am working in EXCEL VBA and I have a large number of strings like this:
str = "aei jt098_374!6.90*9U35t9miK34=90^1-349j582ge&%$g2=045 un=n20#^9i1=n5g76]90 82n3]-568@2^68*)85f1=0947f]287j"
I need to extract all numbers from these strings while recognizing ALL non-numeric characters (all letters and all symbols as delimiters (except for the period (.)). For example, the value of the first several numbers extracted from the example string above should be:
098
374
6.90
9
35
9
I can imagine doing this with the Split function in VBA, however: how does one specify ALL upper and lowercase letters and ALL symbols except for the period (.) as the delimiters (including blank spaces)?
Is there a way to specify EVERYTHING except 0 thru 9 and . as the delimiters for the Split function?
In other words, one can certainly specify the delimiters by positively identifying them in the list of delimiters, but is there a way to negatively identify the delimiters by essentially saying - let the delimiters be EVERYTHING except these listed characters?
Should be doable with regex.
' Under Tools > References
' add a reference to Microsoft VBScript Regular Expressions 5.5
Sub ExtractTheNumbers()
Dim s As String
s = "aei jt098_374!6.90*9U35t9miK34=90^1-349j582ge&%$g2=045 un=n20#^9i1=n5g76]90 82n3]-568@2^68*)85f1=0947f]287j"
Dim regEx As RegExp
Set regEx = New RegExp
With regEx
.Global = True
.MultiLine = False
.Pattern = "\d+\.?\d*"
End With
Dim matches As MatchCollection
Set matches = regEx.Execute(s)
Dim m As Match
For Each m In matches
Debug.Print m.Value
Next
End Sub
returns
098
374
6.90
9
35
9
34
90
1
349
582
2
045
20
9
1
5
76
90
82
3
568
2
68
85
1
0947
287