Solved

Regular expression in vba for excel

Posted on 2010-08-17
7
257 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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…

724 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