Vehicle Number Plate Validation Check

[craignet]

Hi All,

I'm looking for a function which will check if the cell value is a valid UK registration number plate format.

True if it is, False if it isn't.

I've tried myself and had a look around but am unable to find a solution to my problem.

If also possible, if false, to make to necessary amendments to make it a valid format...ie If some lower case values, make them upper case, if there is a space entered, remove it, if a O is entered instead of a 0, correct it and show the corrected value when the function is run. For all that it can't do that with, then leave as False.

I understand the second part may not be possible, even if someone can help with just identifying whether or not the value is a valid format, I would be very grateful. I have something similar which does this for UK postcodes, but am hitting a brick wall trying to do the same for UK registration number plates.

Any help much appreciated.

Craig</SPAN>

 

[General Ledger]

craignet,

Not complete solutions but possibly some pieces to get you going in the right direction:

The UPPER function will convert all lower case letters to upper case. For example =UPPER(A1) where A1 contains 1dg3t will become 1DG32T

The SUBSTITUTE function will replace one character with another. For example =SUBSTITUTE(A1," ","") where A1 contains 1DG 32T will replace all spaces with no character and become 1DG32T

Best of luck,

G/L

 

[Stiuart_W]

I'm sure it is possible but you're going to have to post the rules for a valid number plate format

Eg


Max length
Min Length
Allowed characters


And so on

 

[craignet]

Hi thanks both for the reply, I'm ok with UPPER, removing the spaces and using TRIM/Subsitute etc... but thanks for suggesting.

What I'd like is a function where when referenced would return True or False dependent on if the format was valid.

If any help could the below format examples be used in the function?

Many Thanks

AB12RCY

CD07TES

S33GTT

Y999FVB

CD07TIS

S34GTT

Z999FVB

<TBODY>
</TBODY>

 

[Stiuart_W]

All we can say from the above is that if the last 3 digits are NOT text then it is an invalid format ans it must start with 1 or 2 text digits followed by 2 or 3 numbers. Will all the variations of the above satisfy the format rules? Are all text characters used? What about personalised number plates?

 

[craignet]

All we can say from the above is that if the last 3 digits are NOT text then it is an invalid format ans it must start with 1 or 2 text digits followed by 2 or 3 numbers. Will all the variations of the above satisfy the format rules? Are all text characters used? What about personalised number plates?

Thanks Stiuart,

I believe all variations will satisfy thanks, and characters A-Z, numbers 0-9.
In regards to personalised number plates, the data I will be querying will have very few records of this type so can be checked manually, as I believe there to be too many variations to automate.

Many thanks again.

 

[Peter_SSs]

If the rules are

1 or 2 letters followed by
2 or 3 digits followed by
3 letters

then try this user-defined function. If these are not the rules, then please clarify further.


To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below and copy down.

6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function IsValid(s As String) As Boolean
  Static RX As Object
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.IgnoreCase = True
  End If
  RX.Pattern = "^[A-Z]{1,2}\d{2,3}[A-Z]{3}$"
  IsValid = RX.Test(s)
End Function

Excel Workbook
	A	B
1	Plate	Valid?
2	AB12RCY	TRUE
3	CD07TES	TRUE
4	S33GTT	TRUE
5	Y999FVB	TRUE
6	CD07TIS	TRUE
7	S34GTT	TRUE
8	s34gtt	TRUE
9	Z999FVB	TRUE
10	ABD45TYU	FALSE
11	AB5RCY	FALSE
12	AB1234RTD	FALSE
13	AB66TY	FALSE
14	AB12WRTY	FALSE
15	AB12R0P	FALSE
UK Plates

 

[craignet]

Many thanks Peter, that's just what I was looking for.

Thanks all that helped.

 

[Kyle123]

Here's a tighter option to reduce false positives:

Function IsValid(s As String) As Boolean
  Static RX As Object
  Dim tests, x As Long
  
  tests = Array( _
     "^[A-Z]{2}(5[1-9]|0[2-9]|6[0-9]|1[0-9])[A-Z]{3}$", _
     "^[A-HJ-NP-Y]\d{1,3}[A-Z]{3}$", _
     "^[A-Z]{3}\d{1,3}[A-HJ-NP-Y]$", _
     "^(?:[A-Z]{1,2}\d{1,4}|[A-Z]{3}\d{1,3})$", _
     "^(?:\d{1,4}[A-Z]{1,2}|\d{1,3}[A-Z]{3})$")
     
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
  End If
  For x = LBound(tests) To UBound(tests)
    RX.Pattern = tests(x)
    If RX.test(s) Then
        IsValid = True
        Exit Function
    End If
  Next x




End Function

 

[craignet]

Many Thanks Kyle,

Could I just ask what the function actually does in terms of validation checks?

From what I can make out, there are 5 checks in place, are they 1 or 2 letters followed by 2 or 3 digits followed by 3 letters, and a couple of others?

Or was I missing a couple of validation checks in the original request.

Sorry I haven't used VBScript much so just wanted to understand how the function actually works.


Many Thanks