Regular Expressions - Irish Number Plate

J

JimmyG

Board Regular
Joined
Aug 16, 2005
Messages
70
Hello there!

I'm using the following RegExp test to validate Irish number plates

Code: 
Function IsValidVReg_IE1(value As String) As Boolean
    Dim RE As Object
    Set RE = CreateObject("vbscript.RegExp")
    RE.Pattern = "^\d{2}[A-Z]{1,2}\d{1,6}$"
    IsValidVReg_IE1 = RE.Test(value)
    Set RE = Nothing
End Function


The standard Irish number plate since 1987 has the format YY-CC-SSSSSS, where

• YY — a 2-digit year (e.g. 87 for 1987; 05 for 2005)
• CC — a 1- or 2-character county identifier (e.g. D for Dublin; SO for Sligo).
• SSSSSS — a 1- to 6-digit sequence number

e.g. 00D123


I want to improve my function above to make it more exact, i.e.

YY: only those beginning with 8, 9 or 0
CC: only the following:

C - County Cork
CE - County Clare
CN - County Cavan
CW - County Carlow
D - County Dublin
DL - County Donegal
G - County Galway
KE - County Kildare
KK - County Kilkenny
KY - County Kerry
L - Limerick City
LD - County Longford
LH - County Louth
LK - County Limerick
LM - County Leitrim
LS - County Laois
MH - County Meath
MN - County Monaghan
MO - County Mayo
OY - County Offaly
RN - County Roscommon
SO - County Sligo
TN - North Tipperary
TS - South Tipperary
W - Waterford City
WD - County Waterford
WH - County Westmeath
WX - County Wexford
WW - County Wicklow

Can anyone help me out here? Thanks, JimmyG
 
Last edited by a moderator:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
try
Code: 
Dim myPatn As String
myPtn = "^[089]\d"
myPtn = myPtn & "(C(E|N|W)?|DL?|K(E|K|Y)?|G|L(D|H|K|M|S)?"
myPtn = myPtn & "|M(H|N|O)|RN|SO|T(N|S)|W(D|H|X|W))\d{1,6}$"
RE.Pattern = myPtn
 
Upvote 0
Hi jindon,

Thanks for your quick response... your version in nice and compact.

I also got the following to work...

Code: 
Function CHECKREG_IE(value As String) As Boolean
    Dim RE As Object
    Set RE = CreateObject("vbscript.RegExp")
    RE.Pattern = "^[8|9|0]\d{1}(?:C|CE|CN|CW|D|DL|G|KE|KK|KY|L|LD|LH|LK|LM|LS|MH|MN|MO|OY|RN|SO|TN|TS|W|WD|WH|WX|WW)[1-9]\d{0,5}$"
    CHECKREG_IE = RE.Test(value)
    Set RE = Nothing
End Function


I wasn't sure how to "cluster" the codes together, so now I know :>)
Do this have any performance benefits? -> KE|KK|KY vs K(E|K|Y)?


JimmyG
 
Upvote 0
JimmyG said:
Hi jindon,

Do this have any performance benefits? -> KE|KK|KY vs K(E|K|Y)?

JimmyG
Click to expand...
KE|KK|KY
K(E|K|Y)?

These are different.
KE|KK|KY = either KE or KK or KY
K(E|K|Y)? = either K or KE or KK or KY

However, when I read back your codition regarding "K", ? shoudn't be there.
it should be
K(E|K|Y)
 
Upvote 0
Hi Jimmy

Some remarks:

. don't use the "|" (or) inside the "[]" (character set), unless you really mean the character "|"

Instead of "[8|9|0]" use "[890]"


. don't need the {1}, it clutters up the expression with no use

Instead of "\d{1}" use "\d"


. You are still accepting invalid years, from 80 to 86, you can be more precise in the years' defnition:

You can use for the years: "(8[789]|(9|0)\d)"

assuming unitl 2009.
 
Last edited:
Upvote 0
Thanks for the tips & improvements pgc01!

I'm finding Regular Expressions extremely useful (whether in VB or in Oracle).
They take a bit of getting used to though!


:>) JimmyG
 
Upvote 0
Same Thing - but Northern Irish Number Plate !

Now I need the same thing for the Northern Irish number plate....

I'm almost there....

Here's the long version.... which works OK

Code: 
Function CHECKREG_NI1_v1(value As String) As Boolean
'NI current - alternative version
'
    Dim RE As Object
    Set RE = CreateObject("vbscript.RegExp")
    RE.Pattern = "^[A-Z](?:AZ|BZ|CZ|DZ|EZ|FZ|GZ|HZ|JZ|KZ|LZ|MZ|NZ|OZ|PZ|RZ|SZ|TZ|UZ|VZ|WZ|XZ|YZ|IA|IB|IG|IJ|IL|IW|JI|OI|UI|XI)[1-9]\d{3}$"
    CHECKREG_NI1_v1 = RE.Test(value)
    Set RE = Nothing
End Function

.... in other words:

1 character (A to Z) followed by

2 characters, either
-> A to H followed by Z, or J to P followed by Z, or R to Y followed by Z; or
-> I followed by A,B,G,J,L or W; or
-> J,O,U or X followed by I

then 4 digits in the range 1000 to 9999


I'd prefer to use a more compact version, so I've tried:

Code: 
Function CHECKREG_NI1(value As String) As Boolean
'NI current
'
    Dim RE As Object
    Set RE = CreateObject("vbscript.RegExp")
    RE.Pattern = "^[A-Z]([A-HJ-PR-Y]Z|I(A|B|G|J|L|W)|(J|O|U|X)I)?[1-9]\d{3}$"
    CHECKREG_NI1 = RE.Test(value)
    Set RE = Nothing
End Function

But this lets P4274 through :>(
And I've also seen that a version QNI, then 4 digits is also valid

Regards, JimmyG
 
Upvote 0
Hi JimmyG

Just remove the question mark from the pattern and it should work.

2 remarks:

1 - The "|" is less efficient than the character set. Since in this case you are testing single characters, I think it's better:

Code: 
    RE.Pattern = "^[A-Z]([A-HJ-PR-Y]Z|I[ABGJLW]|[JOUX]I)[1-9]\d{3}$"

2 - I would not use Value as the name of the variable. Value is the name of a property of many objects. When you choose a name for a variable it's good practice not to use names that have a special meaning in the context, like in this case names of objects, properties or methods
 
Upvote 0
Thanks pgc01!

I've included the QNI9999 variant, and this works nicely...

Code: 
RE.Pattern = "^(QNI|[A-Z]([A-HJ-PR-Y]Z|I[ABGJLW]|[JOUX]I))[1-9]\d{3}$"
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Pivot table not sum up the column
Replies
0
Views
992
shido
S

Forum statistics

Threads
1,215,759
Messages
6,126,728
Members
449,332
Latest member
nokoloina

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top