CODEWITHSUNDEEP
vbaexcel
Anthony
Anthony

Reputation: 923

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

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?

Upvotes: 0

Views: 1093

Answers (3)

Dick Kusleika
Dick Kusleika

Reputation: 33165

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

Upvotes: 2

twneale
twneale

Reputation: 2946

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).

Upvotes: 1

Lance Roberts
Lance Roberts

Reputation: 22842

In VBA:

Split(inputstring)

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

This dupe has a little more info.

Upvotes: 2

Related Questions