Validate UK National Insurance Numbers (NINO's)

[Mashtonsmith]

Hello...

I have large file with over 200k NINO's detailed. One test I need to run is to validate these NINO's to esnure the correct format has been used. I dont want to correct them, simply identify those who fail the format test.

After searching the forum I;ve not found any answers that cover this question.

The format rules are as follows.

National Insurance Number

1. Must be 9 characters.
2. First 2 characters must be alpha.
3. Next 6 characters must be numeric.
4. Final character can be A, B, C, D or space.
5. First character must not be D,F,I,Q,U or V
6. Second characters must not be D, F, I, O, Q, U or V.

At the very least I'd like to be able to test 1-4.

Please help.

Thanks

 

[Mashtonsmith]

Sorry - I see what you've done now (taken the letters out of the string condition)

I really need to leave the full alphabet in the initial module for both S1 and S2.

S1 and S2 should simply test that these characters are alpha.

The 2nd module should test the correct letters for characters 1 and 2.

 

[shg]

Function NINO1(sInp As String) As Boolean
    Const s1 As String = "?"
    Const s2 As String = "?"
    Const s3 As String = "######"
    Const s4 As String = "[ABCD ]"
    
    NINO1 = sInp Like s1 & s2 & s3 & s4
End Function
 
Function NINO2(sInp As String) As Boolean
    Const s1 As String = "[ABCEGHJKLMNOPRSTWXYZ]"
    Const s2 As String = "[ABCEGHJKLMNPRSTWXYZ]"
    Const s3 As String = "?"
    Const s4 As String = "?"
    
    NINO2 = sInp Like s1 & s2 & s3 & s4
End Function

 

[Mashtonsmith]

ok for some reason AB123456C is failing the 2nd test..?

And this return as true on A1123456C for NINO1??

 

[shg]

Function NINO2(sInp As String) As Boolean
    Const s1 As String = "[ABCEGHJKLMNOPRSTWXYZ]"
    Const s2 As String = "[ABCEGHJKLMNPRSTWXYZ]"
[COLOR=red]   Const s3 As String = "*"[/COLOR]
[COLOR=red]   Const s4 As String = "*"[/COLOR]
    
    NINO2 = sInp Like s1 & s2 & s3 & s4
End Function

      ----A---- --B-- --C--
  1             NINO1 NINO2
  2   AB123456C  TRUE  TRUE
  3   A1123456C  TRUE FALSE

 

[Mashtonsmith]

ok NINO2 works but NINO1 doesn't - can I just put all letters of apphabet in place of "?"

Function NINO1(sInp As String) As Boolean
Const s1 As String = "?"
Const s2 As String = "?"
Const s3 As String = "######"
Const s4 As String = "[ABCD ]"

NINO1 = sInp Like s1 & s2 & s3 & s4
End Function

Function NINO2(sInp As String) As Boolean
Const s1 As String = "[ABCEGHJKLMNOPRSTWXYZ]"
Const s2 As String = "[ABCEGHJKLMNPRSTWXYZ]"
Const s3 As String = "*"
Const s4 As String = "*"

NINO2 = sInp Like s1 & s2 & s3 & s4
End Function

 

[shg]

What's an example of where it fails?

 

[Mashtonsmith]

have made this change and it seems to work. Is this ok?

Function NINO1(sInp As String) As Boolean
Const s1 As String = "[ABCDEFGHIJKLMNOPQRSTUVWXYZ]"
Const s2 As String = "[ABCDEFGHIJKLMNOPQRSTUVWXYZ]"
Const s3 As String = "######"
Const s4 As String = "[ABCD ]"

NINO1 = sInp Like s1 & s2 & s3 & s4
End Function

 

[shg]

If it works for you, it's fine with me.

 

[Mashtonsmith]

As long as its doing as it should - it seems to be.

Many thanks for your help.