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?

Dick Kusleika · Accepted Answer · 2009-09-16 22:37:07Z

2

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

answered Sep 16, 2009 at 22:37

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 10:09:38Z

2

In VBA:

Split(inputstring)

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

This dupe has a little more info.

edited May 23, 2017 at 10:09

CommunityBot

11 silver badge

answered Sep 16, 2009 at 21:36

Lance Roberts

22.9k32 gold badges115 silver badges132 bronze badges

Comments

twneale · Accepted Answer · 2009-09-16 21:37:39Z

1

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

answered Sep 16, 2009 at 21:37

twneale

2,9665 gold badges29 silver badges37 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related