Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Regular expression in vba for excel

Posted on 2010-08-17
7
Medium Priority
?
271 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

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

samplenode.xls
0
 
LVL 81

Accepted Solution

by:
byundt earned 2000 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

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.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

577 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