• Home
 · 
• String Manipulation
 · 
• This code checks to ensure that a cell in excel in column W is a valid UK National Insurance Number

This code checks to ensure that a cell in excel in column W is a valid UK National Insurance Number

This code checks to ensure that a cell in excel in column W is a valid UK National Insurance Number

Rate This code checks to ensure that a cell in excel in column W is a valid UK National Insurance Number

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

Dim stringNI As String

For a = 2 To 6706 ' set loop for how many rows

stringNI = NoSpaces(Cells(a, 24)) ' removes all spaces
If Len(stringNI) <> 9 Then ' if the lengh is not 9 then ni is not valid
Cells(a, 1) = "ERR"
Else

   
        If IsNumeric(Mid(stringNI, 1, 2)) = True Or IsNumeric(Mid(stringNI, 3, 6)) = False Or IsNumeric(Right(stringNI, 1)) = True Then ' checks correct format

            Cells(a, 1) = "ERR"
        Else
        Cells(a, 1) = ""
        End If

End If

Next a

End Sub