Solved

Regular expression in vba for excel

Posted on 2010-08-17
7
239 Views
Last Modified: 2012-05-10
I looking stumped on how to write the regexp for this word pattern "Node XXX 200Z"  where "x" could be 1-999  and "z" could be any number 1-9
0
Comment
Question by:rpelfrey
  • 3
  • 3
7 Comments
 
LVL 4

Expert Comment

by:javaftper
ID: 33456927
I have written a sub to call a function which returns 'No match' if the pattern fails or the node name if a match is found-

Sub TestText()
    Const strTest As String = "Node 769 2009" 'test value
    MsgBox REGEXTEST(strTest)
End Sub

Function REGEXTEST(strData As String) As String
    Dim RE As Object, REMatches As Object

    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = False
        '.IgnoreCase = True if necessary you can ignore case
        .Pattern = "(Node )[1-9][1-9][1-9]( )(200[1-9])"
    End With
   
    Set REMatches = RE.Execute(strData)
    If REMatches.Count = 0 Then
    REGEXTEST = "No match"
    Else
    REGEXTEST = REMatches(0)
    End If
   

End Function
0
 
LVL 81

Expert Comment

by:byundt
ID: 33457189
Assuming that the pattern ought to allow for "Node 900 2008", I modified javaftper's code with the following statement:
 .Pattern = "Node\s([1-9]\d{2})\s200([1-9])"
0
 
LVL 81

Expert Comment

by:byundt
ID: 33457215
My pattern put the xxx and y parts of the pattern within parentheses so you can retrieve the submatches separately. With reference to javaftper's Else statement:
    REGEXTEST = REMatches(0).submatches(0) & " " & REMatches(0).submatches(1)   'Retrieve xxx and y separately
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:rpelfrey
ID: 33457435
I am getting "No Match" see attached

samplenode.xls
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 33457656
Change the pattern as follows:
.Pattern = "Node\s+([1-9]\d{0,2})\s+200([1-9])"

All nodes will match, even those with more than one space. If you require exactly one space, then remove the two + from the pattern.
0
 

Author Comment

by:rpelfrey
ID: 33457941
That did it.  I was using a vb script  that I had found that allows you to define the pattern while implementing the search/function (=RegExpFind(B1,"Node\s+([1-9]\d{0,2})\s+200(9)").  

It was written by matthewspatrick at this site http://www.vbaexpress.com.  I just could not defined the pattern properly.
0
 

Author Closing Comment

by:rpelfrey
ID: 33457950
Great Job!!!!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

813 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now