Performs structural validation of PAN (validations explained at the end)


Cell Reference or Text containing PAN

name(String) [optional]
Name of the entity to validate with PAN. Based on 4th character of PAN, 5th character of PAN is validated against entity name as below

  • If 4th character is found as P i.e. Proprietor, then name is split into words & last word is treated as surname. First letter of surname should be 5th character of PAN
  • For all other cases, 1st letter of entity name must be 5th character of PAN


(Boolean) Validation result as TRUE / FALSE

Target Platform:

Function ISVALIDPAN(pan As String, Optional name As String = "") As Boolean
Dim expr As New RegExp
expr.Pattern = "^[A-Z]{3}([ABCFGHLJPT])[A-Z][0-9]{4}[A-Z]$"
If Not expr.Test(pan) Then
    ISVALIDPAN = False
    Exit Function
End If

If name = "" Then 'No name found, treat it as optional and skip validation
    Exit Function
End If

Dim pan_4 As String, pan_5 As String, name_1 As String
pan_4 = Strings.Mid(pan, 4, 1)
pan_5 = Strings.Mid(pan, 5, 1)
name = Strings.UCase(name)
If pan_4 = "P" Then
    Dim parts() As String
    name = Strings.Trim(name)
    parts = Strings.Split(name, " ") 'split name into parts
    name_1 = Strings.Left(parts(UBound(parts)), 1) 'extract first letter of surname
    name_1 = Strings.Left(name, 1) 'extract first letter of entity name
End If
ISVALIDPAN = (pan_5 = name_1)

End Function

Variation 1: Pure validation of PAN number structurally
script library isvalidpan example pure validation

Variation 2: Extended validation with entity name (optional)
script library isvalidpan example extended validation


Function runs through following checks (as shown in image)
script library isvalidpan validation checks

Dependency (VBA)

This Excel VBA Macro uses Regular Expression feature, which is not available by default. So it has to be referenced in VBA Project using below steps

  1. In Excel press Alt + F11 which will open VBA Macro editor
  2. Go to Tools > References which will open VBA Project References dialog
  3. Locate & Tick Microsoft VBScript Regular Expression 5.5 as shown in below image
    script library regular expression dependency screenshot
  4. Click OK & close VBA Window
To write any comments or post queries, kindly sign-in to your Facebook & then refresh this page