Regular Expressions in VBA

• • Reply

  13 years ago

  by sajiv

  Sajiv

  USA, United States

  Joined 13 years ago

  So I have regular expressions working in my VBA code.  For the example input below I would like to match the following:
  

  
  Input - FNB 3.875 2/10  30 /296 ( 5/10)    5YR \End Input    What I would like matched 2 and 10 and 5 from 5YR  
  Input - MCR 4.50  1/15  376/36  ( 5/5 )    \End Input     10Y  What I would like matched 376 and 36 and 10 from 10YR  
  Input - FNB 5.00  4/15  396/39  ( 5/10)    \End Input    10Y  What I would like matched 396 and 39 and 10 from 10YR  
  

  
  This is what I currently have:
  

  
  Dim objRegExp As RegExp
  Dim objMatch As Match
  Dim colMatches As MatchCollection
  Dim RetStr As String
  Dim myarray
  Dim Txt As String
  RegExp = " "
  

  
  

  
  'Pattern for Bid/Offer
  

  
  mypattern = "\w+|w/d|d+\b (d|s|d+s/d+)|(s|d|d+)')'\B\b(d+|d)Y|YR"
  

  
  'Create a regular expression object.
    Set objRegExp = New RegExp
  

  
  'Set the pattern by using the Pattern property.
    objRegExp.Pattern = mypattern
  

  
  'Set Case Insensitivity.
    objRegExp.IgnoreCase = True
  

  
  'Set global applicability.
    objRegExp.Global = True
  

  
  

  
  'Test whether the String can be compared.
    If (objRegExp.Test(Lkp) = True) Then
  Set colMatches = objRegExp.Execute(Lkp)   ' Execute search.
  

  
  Set oMatch = colMatches(0)
   ' Create the results string.
   
   RetStr = RetStr & oMatch.SubMatches(0)
   RetStr = RetStr & oMatch.SubMatches(1)
   SubMatchTest = RetStr
     MsgBox SubMatchTest
     
  End If
  

  
  Nothing Appears in the Return String!

  Report abuse
• • Reply

  13 years ago

  by James Crowley

  James Crowley

  London, United Kingdom

  Joined 18 years ago

  Have you tried using an online regex tester like the following?
  

  
  http://www.regexlib.com/RETester.aspx
  

  
  This will let you experiment with different inputs and regex strings, so you should be able to break down the pattern and figure out what's going on? Just having a quick look at your string, for one thing you can replace things like
  

  
  (d+|d)
  

  
  with just (d+)  as the + sign means "one or more"

  Report abuse
• • Reply

  13 years ago

  by sajiv

  Sajiv

  USA, United States

  Joined 13 years ago

  good call....wil give that a shot

  Report abuse
• • Reply

  13 years ago

  by sajiv

  Sajiv

  USA, United States

  Joined 13 years ago
  The real issue boils down to coming up with a regular expression for this pattern.
  
  Input:
  
  FNB 3.875 2/10  30/296 ( 5/10)    5YR
  MCR 4.50  1/15  376/36  ( 5/5 )   10YR
  FNB 5.00  4/15  396/39  ( 5/10)   10YR
  CMC 3.75  8/07     /    (  /      2YR
  FNB 7.125 1/30     /45  (  /5 )    30Y
  HLG 2.625 O/06  11 /    ( 5/      2YR
  HLG 3.625 1/08  25 /246 ( 5/5 )    3YR
  
  Desired output:
  
  30,296,5
  376,36,10
  396,39,10
  ,,2
  ,45,30
  11,,2
  25,246,3 (notice the space between 25 and the /)
  
  The current regular expression I have is:
  
  [A-Z]{3}\s[\d+\.]+\s+\w+/\d+\s+(\d+)/(\d+)\s+[ \(]+\d+/\d+[\)]\s+(\d+)Y|YR
  
  This will not cover all the cases.  Let me know if you guys have any thoughts
  Report abuse
• • Reply

  13 years ago

  by Uncle

  Uncle

  England, United Kingdom

  Joined 17 years ago

  Rather than using regular expressions, you could use this VB to extract the values:
  

  
  

  Code:

  
  Dim strTest As String
  Dim strRes1 As String
  Dim arrBits() As String
  Dim arrOne() As String
  Dim arrTwo() As String
  Dim arrThree() As String
  Dim arrFour() As String
  

  
  strTest = "HLG 3.625 1/08  25 /246 ( 5/5 )    3YR "
  arrBits() = Split(strTest, "/")
  arrOne() = Split(arrBits(1))
  strRes1 = Trim(arrOne(2)) & ","
  arrTwo() = Split(arrBits(2))
  strRes1 = strRes1 & Trim(arrTwo(0)) & ","
  arrThree() = Split(arrBits(3), ")")
  arrFour() = Split(arrThree(1), "Y")
  strRes1 = strRes1 & Trim(arrFour(0))
  

  
  

  
  Not the most elegant piece of code, but it returns the correct results from the different test strings you have posted!

  Report abuse
• • Reply

  13 years ago

  by stevey

  Stephen York

  , Australia

  Joined 13 years ago

  Hello,
  

  
  Not sure if you solved the problem yet, but here's what you need:
  

  
  s/^[^\/]\/[^ ] ([0-9]) \/ *([0-9])[^(](. ([0-9])[Yy][Rr]$/$1,$2,$3/
  Note:   \/ is an escaped forward slash (backslash then a forward slash)
  I had to put the \ in so I could could test the expression in perl.
  I originally wrote it in sed, so it's pass 2 tests.
  

  
  If you want a detailed description (as it is quite obscure) the let me know.
  

  
  

  
  

  
  

  
  Steve

  Report abuse