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
260 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
Comment Utility
Can anything (ie a non-digit character) occur before the digits?
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
Comment Utility
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
Comment Utility
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
 
LVL 35

Expert Comment

by:Terry Woods
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:gabrielPennyback
Comment Utility
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
Comment Utility
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
Comment Utility
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)
Comment Utility
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
Comment Utility
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)
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now