[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Regex Expression with Excel VBA

Posted on 2015-01-09
7
Medium Priority
?
182 Views
Last Modified: 2015-01-11
How to modify the regex expression in order to handle an additional case, -93ASV-0.59 and break it down into 3 parts:
(1) -93
(2) ASV
(3) -0.59
 
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "(\d+)([A-Z]+)(\d+.\d+)"
    If regex.test(theString) Then
        Set matches = regex.Execute(theString)
        arr(0) = matches.Item(0).SubMatches(0)
        arr(1) = matches.Item(0).SubMatches(1)
        arr(2) = matches.Item(0).SubMatches(2)
        getPart = arr
    End If

Result
Input  
   11AFD7.69
Output
    arr(0) = 11
    arr(1) = AFD
    arr(2) = 7.69
0
Comment
Question by:AXISHK
  • 3
  • 2
  • 2
7 Comments
 
LVL 85

Accepted Solution

by:
ozo earned 1200 total points
ID: 40541679
"(-?\d+)([A-Z]+)(-?\d+\.\d+)"
0
 

Author Comment

by:AXISHK
ID: 40541832
Is there any reference for Regex expression for VBA ? Is the syntax is the similar as other language ?
0
 

Author Comment

by:AXISHK
ID: 40541858
BTW
What's mean for -?, no need to add \ in front of it ?

http://msdn.microsoft.com/en-us/library/ms974570.aspx

Is there nay reference on the regex usage ? I can't find the meaning for "-"  ? Tks
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 48

Assisted Solution

by:Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs) earned 800 total points
ID: 40543423
A dash has no special meaning in Regex so does not need to be escaped with the back-slash.

"-?" is looking for zero or more dashes so that the pattern -?\d+ will match -93 or 93.
0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 40543428
A good article on Regex in VBA, written by Excel genius Patrick Matthews, can be found here.
0
 
LVL 85

Expert Comment

by:ozo
ID: 40543437
"-?" is looking for zero or one dashes
"-*" would look for zero or more dashes
"-+" would look for one or more dashes

A dash can have special meaning inside [],  e.g. "[A-Z]"
0
 

Author Closing Comment

by:AXISHK
ID: 40543550
Tks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question