How To Verify A UK National Insurance Number With Excel

[CA Admin]

Courtesy of SofiaM

To verify the validity of a National Insurance (NI) number using Excel:

Open a new Excel spreadsheet.
Enter the NI number in cell A2.
In the formula bar, input the following formula:


=IF(
  AND(
      LEN(A2)=9,
      OR(LEFT(A2,1)={"a","b","c","e","g","h","j","k","l","m","n","o","p","r","s","t","w","x","y","z"}),
      OR(MID(A2,2,1)={"a","b","c","e","g","h","j","k","l","m","n","p","r","s","t","w","x","y","z"}),
      NOT(OR(LEFT(A2,2)={"BG","GB","KN","NK","NT","TN","ZZ"})),
      ISNUMBER(--MID(A2,3,6)),
      OR(RIGHT(A2,1)={"a","b","c","d"})
  ),
  "Valid NI Number",
  "Not a valid NI Number"
)

After entering the formula, a cell will display either "Valid NI Number" or "Not a valid NI Number" based on the input.

[SofiaM]

I just want to add a detail to this.

It doesn't confirm only NI Numbers in circulation, it satisfies what is actually a very simple algo check of what is or will be a valid NI number now or in the future.

Think Voters' roll registration for example. You don't want to cock it up with some blag random NI Number that the council will know is wrong. Use this spreadsheet and your number will pass the algo check.