vba string tokens

Question

I have around 100 rows of such texts that I want to tokenize:

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

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

Thanks in advance.

Answer 1

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 2

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 3

VBA split function right from MS's page

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