Checking cell format

[blackie42]

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]

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]

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]

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]

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]

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]

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]

Thanks Guys - does what I need it to do

regards
Jon