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

by Graham Hayes (3 Submissions)
Category: String Manipulation
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Wed 18^th May 2005
Date Added: Mon 8^th February 2021
Rating: (1 Votes)

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



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

Download this snippet Add to My Saved Code

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

No comments have been posted about This code checks to ensure that a cell in excel in column W is a valid UK National Insurance Number. Why not be the first to post a comment about 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 Comments

Post your comment

Difficulty