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

Answer 2

You could read line by line and use the split function to split the word and number by space. I vaguely remeber VBA has the split function.

I got the following link by searching in google. Not sure which version of office you are using.

http://msdn.microsoft.com/en-us/library/aa155763(office.10).aspx

This link has the split function.

Answer 3

VBA split function right from MS's page

http://msdn.microsoft.com/en-us/library/aa155763(office.10).aspx