Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
269 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
[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
  • 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 800 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 1200 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

Hire Technology Freelancers with Gigs

Work with 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.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
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 demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

704 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