EXCEL VBA Split Function - Specifying ALL Symbols and Letters as Delimiters

Question

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:

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?

score 6 · Accepted Answer · answered Apr 13 '23 at 20:07

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

EXCEL VBA Split Function - Specifying ALL Symbols and Letters as Delimiters

1 Answers1