Tokenizing Strings

Question

I have around 100 rows of text that I want to tokenize, which are alike the following:

<word> <unknown number of spaces and tabs> <number>

I am having trouble finding tokenize functions with VBA. What would be the easiest method to tokenize such strings in VBA?

Answer 1

You can use the Split() method or for more complex matches, you can use the "vbscript.regexp" object:

Sub NewRegex()
    Dim reg
    Dim matches, match, tmpStr As String

    Set reg = CreateObject("vbscript.regexp")
    tmpStr = "blah bla ...."

    With reg
        .IgnoreCase = True
        .MultiLine = False
        .Pattern = "your regex pattern goes here"
        .Global = True
    End With

    Set matches = reg.Execute(tmpStr)

    For Each match In matches
        MsgBox match
    Next mt

End Sub

Here's a tutorial on using regex from VBA: Using Regular Expressions (RegExp) in Excel