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

 

[shg]

User-defined function?

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

E.g., =GoodNINO(A1)

 

[Mashtonsmith]

Sorry - I dont understand this..

 

[Sandeep Warrier]

How about

Function ValidateNINO(sInp As String) As Boolean
    Dim REX As Object
    ValidateNINO = False
    Set REX = CreateObject("VBScript.RegExp")
    With REX
        .IgnoreCase = True
        .Pattern = "[ABCEGHJKLMNOPRSTWXYZ]{2}\d{6}[ABCD ]"
        If .Test(sInp) Then
            ValidateNINO = True
        End If
    End With
End Function

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="10px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">NINO</td><td style="font-weight: bold;;">Validation</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">CE987548B</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">VQ985236132</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">AB985124C</td><td style="text-align: right;;">TRUE</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=ValidateNINO(<font color="Blue">A2</font>)</td></tr></tbody></table></td></tr></table><br />

 

[shg]

Add the code to a code module, and then use it as you would a worksheet function, as in the example.

Adding a Macro to a Code Module<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
1. Copy the code from the post<o></o>
2. Press Alt+F11 to open the Visual Basic Editor (VBE)<o></o>
3. From the menu bar in the VBE window, do Insert > Module<o></o>
4. Paste the code in the window that opens<o></o>
5. Press Alt+Q to close the VBE and return to Excel

EDIT: Sandeep, the first two characters are different in the original post; the second excludes O.
<o></o>

 

[Sandeep Warrier]

@shg - Ahh yes, my bad! Thanks for pointing it out!

In that case, the pattern should be

.Pattern = "[ABCEGHJKLMNOPRSTWXYZ][ABCEGHJKLMNPRSTWXYZ]\d{6}[ABCD ]"


One hideous formula solution (works only on capital alphabets)

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="10px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">NINO</td><td style="font-weight: bold;;">Validation</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">CE987548C</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">VQ985236132</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">AB985124C</td><td style="text-align: right;;">TRUE</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=IF(<font color="Blue">LEN(<font color="Red">A2</font>)=9,IF(<font color="Red">ISNA(<font color="Green">MATCH(<font color="Purple">CODE(<font color="Teal">LEFT(<font color="#FF00FF">A2</font>)</font>),{68,70,73,81,85},0</font>)</font>)*ISNA(<font color="Green">MATCH(<font color="Purple">CODE(<font color="Teal">MID(<font color="#FF00FF">A2,2,1</font>)</font>),{68,70,73,81,85},0</font>)</font>),IF(<font color="Green">ISNUMBER(<font color="Purple">MID(<font color="Teal">A2,3,6</font>)+0</font>),IF(<font color="Purple">ISNUMBER(<font color="Teal">MATCH(<font color="#FF00FF">CODE(<font color="Navy">RIGHT(<font color="Blue">A2</font>)</font>),{32,65,66,67,68},0</font>)</font>),TRUE,FALSE</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[shg]

Or maybe

=(LEN(A1)=9)
* ISNUMBER(SEARCH(LEFT(A1), "ABCEGHJKLMNOPRSTWXYZ"))
* ISNUMBER(SEARCH(MID(A1,2,1), "ABCEGHJKLMNPRSTWXYZ"))
* ISNUMBER(--MID(A3, 3, 6))
* ISNUMBER(SEARCH(LEFT(A1), "ABCD "))

Change SEARCH to FIND to make it case-sensitive.

That's not perfectly rigorous about the numeric part.

 

[Mashtonsmith]

shg - thanks that working now. Any chance this could be extended to include points 5 and 6 in a separate module?

 

[shg]

What works -- the formula or the function?

I don't know what you mean about separating it.

 

[Mashtonsmith]

ok - conditions 5 and 6 satisfy the core formating rules but fail on the letter rules.

Can a module be written to separately test just for 5 and 6?