Solved

RegEx code that limits digit matches to 3 digits, ignoring entirely strings of 4 or more contiguous digits

Posted on 2011-03-24
12
264 Views
Last Modified: 2012-05-11
How do I modify the line of code below so that it ignores ALL digits that occur in strings of more than 3? For example:

"278456 ABC" will currently return "456 ABC", whereas I want it to return nothing.

Thanks,
John
.Pattern = "\d{1,3}[ /-]?(?!jan|feb|apr|ma[ry]|ju[ln]|aug|sep|oct|nov|dec)[A-M]+"

Open in new window

0
Comment
Question by:gabrielPennyback
  • 6
  • 4
  • 2
12 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 35212080
Can anything (ie a non-digit character) occur before the digits?
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35212083
Try this pattern:

^(\d{1,3})[ /-]?(?!jan|feb|apr|ma[ry]|ju[ln]|aug|sep|oct|nov|dec) [A-M]+$

Kevin
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 200 total points
ID: 35212164
The solution I posted above assumes the digits start a line. This one looks for one or more sequences of three digits followed by a space, slash, or dash following by any number of letters A through M but not month names.

(?:^|[-\d])\d{1,3}[ /-]?(?!jan|feb|apr|ma[ry]|ju[ln]|aug|sep|oct|nov|dec) [A-M]+

Kevin
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 35

Expert Comment

by:Terry Woods
ID: 35212177
Kevin, I think your latest suggestion should be adjusted to:
(?:^|\D)\d{1,3}[ /-]?(?!jan|feb|apr|ma[ry]|ju[ln]|aug|sep|oct|nov|dec)[A-M]+

Note I also removed a space before the [A-M] which would have caused it to fail in some cases.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 35212202
Note that that pattern will include a non-digit character that occurs before the digits in the match though

Ideally, you'd use a negative lookbehind, like this:
(?<!\d)\d{1,3}[ /-]?(?!jan|feb|apr|ma[ry]|ju[ln]|aug|sep|oct|nov|dec)[A-M]+

Or alternatively you could use a capturing group for the remainder of the pattern so you can extract your result from the first capturing group:
(?:^|[-\d])(\d{1,3}[ /-]?(?!jan|feb|apr|ma[ry]|ju[ln]|aug|sep|oct|nov|dec) [A-M]+)
0
 
LVL 35

Accepted Solution

by:
Terry Woods earned 300 total points
ID: 35212213
Typo corrected for that last pattern, which still had the space character copied from Kevin's pattern:
(?:^|[-\d])(\d{1,3}[ /-]?(?!jan|feb|apr|ma[ry]|ju[ln]|aug|sep|oct|nov|dec)[A-M]+)
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 35212364
Once again, you guys are amazing, thanks. For some reason I got errors on everything before Terry's last suggestion, but that one nails it. Thank you for your collaboration.

Now if I may add one more thing regarding the letters. Sometimes I get text like "level 1 check ..."  so I need something (I think in the first Pattern) to prevent that.

      .Pattern = "(?:ROW|SEAT)(S)?\s*(\d+)\s*([,-/|]|to|thru|through)\s*(\d+)"

Thanks,
John
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 35212424
Actually I just noticed a bug in the last solution. It excludes strings with 1 digit only.  I ran it on this free text:

      MNL / DGL 9K 7K 31A 32B 38J 36H 20A 24C 37D 51H 1SR / R

It should produce this: 9K, 7K, 31A, 32B, 38J, 36H, 20A, 24C, 37D, 51H
But now I get this: 31A, 32B, 38J, 36H, 20A, 24C, 37D, 51H

I noticed that if I change "(\d{1,3}" to "(\d{0,3}", it picks up 9K and 7K, but then it also picks up the first letter "M".

How can I fix that?

- John
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 35212434
That pattern requires ROW or SEAT so I don't see how "level 1 check" can get through
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35212735
This works with the string provided:

Public Sub Test()

   Dim RegExp As Object
   Dim Matches As Object
   Dim Text As String
   Dim Index As Long
   
   Text = "MNL / DGL 9K 7K 31A 32B 38J 36H 20A 24C 37D 51H 1SR / R"
   
   Set RegExp = CreateObject("vbscript.regexp")
   RegExp.Global = True
   RegExp.MultiLine = True
   RegExp.IgnoreCase = True
   
   RegExp.Pattern = "(?:^|\s)(\d{1,3}[A-M])"
   
   Set Matches = RegExp.Execute(Text)
   If Matches Is Nothing Then Exit Sub
   If Matches.Count = 0 Then Exit Sub
   
   For Index = 1 To Matches.Count
      MsgBox Matches(Index - 1).Value
   Next Index

End Sub

Kevin
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 35212749
Expanding on zorvek's code, this would cover cases with commas, dashes etc between the values:

(?:^|\D)(\d{1,3}[A-M])
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35212808
Further, this string only matches a single character after one to three digits followed by a space or comma:

   (?:^|\D)(\d{1,3}[a-m])(?:$|([ ,]))

Kevin
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel VBA 10 39
MS excel check if Links exist 5 35
conditional formatting 4 42
Using an Average(IF statement to return values from closed external workbooks 5 19
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

776 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