regular expression in vba excel looking for groups out of a string

Question

I am currently working on a vba project that has the end user copy/paste long strings of text into a worksheet and the code to parse out data from the junk in these strings and organize it from them.

The strings will always be in different lengths, and have a different number of spaces between the data. However they will always be grouped the same way(i.e. the price comes first, some white space, unit price, some white space, and the id number). Is there a regular expression that will just pull the groups(both letters and numbers) out of the whitespace?

Answer 1

In VBA:

Split(inputstring)

You can also set a different delimiter, but it uses space by default.

This dupe has a little more info.

Answer 2

I don't know quite how re syntax works in excel VBA, but in python (which is PERL-like), the simplest regular expression would be:

\S+

This would match any sequence of non-whitespace characters, and in python I would use it's findall method to grab all matches from the document.

If excel VBA doesn't a simple way to do that, I would heartily recommend ditching excel for python (but I admit excel is great and easy for parsing).

Answer 3

If you just want to remove consecutive space delimiters, you might use Text To Columns.

    MyRange.TextToColumns Destination:=MyRange.Cells(1), _
        DataType:=xlDelimited, _
        ConsecutiveDelimiter:=True, _
        Space:=True

Then you could read your values out of the cells of the destination range, MyRange.Cells(1).CurrentRegion