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
265 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

809 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