View Full Version : [SOLVED:] Checking cell format
blackie42
01-09-2015, 01:59 AM
Hi,
I need to check some data is in the right format.(Big spreadsheet hence need for macro)
Column A should be in the format 'QQ123456A' (which is the national insurance number) and the last letter can only be A,B,C or D
so AB416586D would be OK whereas AB416586E wouldn't nor AB41658D (one number missing).
Maybe a series of macros - check length is 9 characters (if not fail), if passes check first 2 characters are letters (if not fail) etc - unless theres a better way??
Any not in right format should be highlighted (to show as error)
Any help appreciated
thanks
Jon
Bob Phillips
01-09-2015, 06:36 AM
Try a formula
=AND(LEFT(A2,2)>="AA",LEFT(A2,2)<="ZZ",ISNUMBER(--(MID(A2,3,6))),ISNUMBER(MATCH(RIGHT(A2,1),{"A","B","C","D"},0)))
blackie42
01-09-2015, 07:47 AM
Thanks Bob,
AB123456CD returns as TRUE (10 characters)
It works otherwise but just to throw a spanner in the works (and having read the HMRC guidance) it appears that there are several exceptions as follows:
The letters D,F,I,Q,U,V can not be used as the first or second prefix letter and O can no be used as the 2nd prefix letter.
I also have issues where the formula passes the NINO as TRUE but is clearly wrong e.g AA111111A and duplicates (but I can manage these by sorting the sheet)
Not sure if you can help further?
thanks
Jon
mikerickson
01-10-2015, 06:53 AM
Try
If (testString Like "[A-Z][A-Z]######[ABCD]") And (testString Like "[!DFIQUV][!DFIQUVO]*") Then
MsgBox "good string"
End If
Why is AA111111A wrong?
blackie42
01-12-2015, 03:23 AM
Hi Mike - experience tells me that AA111111A is not a current valid NINO (its likley to be the first one ever issued)
I picked up a UDF off another forum which seems to cover the exceptions - just doesn't find any that aren't 9 in length so
using another formula in the next column =(LEN(A3)=9). The UDF..
Function ValidateNINO(sInp As String) As Boolean
Dim REX As Object
ValidateNINO = False
Set REX = CreateObject("VBScript.RegExp")
With REX
.IgnoreCase = True
.Pattern = "[ABCEGHJKLMNOPRSTWXYZ][ABCEGHJKLMNPRSTWXYZ]\d{6}[ABCD ]"
If .Test(sInp) Then
ValidateNINO = True
End If
End With
End Function
Be good if I could get it (the length checker) all in one function - but I ain't that clever!
thanks
Bob Phillips
01-12-2015, 04:45 AM
You could add the test to the UDF
Function ValidateNINO(sInp As String) As Boolean
Dim REX As Object
ValidateNINO = False
If Len(sInp) = 9 Then
Set REX = CreateObject("VBScript.RegExp")
With REX
.IgnoreCase = True
.Pattern = "[ABCEGHJKLMNOPRSTWXYZ][ABCEGHJKLMNPRSTWXYZ]\d{6}[ABCD]"
If .Test(sInp) Then
ValidateNINO = True
End If
End With
End If
End Function
Bob Phillips
01-12-2015, 04:53 AM
We did this a while back, and I came up with this pattern
Function ValidateNINO(sInp As String) As Boolean
Dim REX As Object
ValidateNINO = False
Set REX = CreateObject("VBScript.RegExp")
With REX
.IgnoreCase = True
.Pattern = "^[A-CEGHJ-PR-TW-Z]{1}[A-CEGHJ-NPR-TW-Z]{1}[0-9]{6}[A-DFM]{0,1}$"
If .Test(sInp) Then
ValidateNINO = True
End If
End With
End Function
blackie42
01-13-2015, 03:05 AM
Thanks Guys - does what I need it to do
regards
Jon
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.