Search code examples
excelvbastringtext-extraction

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


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?


Solution

  • 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