Performs structural validation of PAN (validations explained at the end)
pan(String)
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
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
ISVALIDPAN = True
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
Else
name_1 = Strings.Left(name, 1) 'extract first letter of entity name
End If
ISVALIDPAN = (pan_5 = name_1)
End Function
/**
* Structurally validates if specified PAN is correct or not
* @param {string} pan PAN
* @param {string} name Name of entity (optional)
* @return TRUE/FALSE
* @customfunction
*/
function ISVALIDPAN(pan, name = '') {
if (!/^[A-Z]{3}([ABCFGHLJPT])[A-Z][0-9]{4}[A-Z]$/g.test(pan))
return false;
if (!name) //No name found, treat it as optional and skip validation
return true;
let pan_4 = pan.substring(3, 4);
let pan_5 = pan.substring(4, 5);
let name_1 = '';
name = name.toUpperCase();
if (pan_4 == 'P') { //Individual
let parts = name.trim().split(' '); //split name into parts
name_1 = parts[parts.length - 1].substring(0, 1); //extract first letter of surname
}
else //Other name individual
name_1 = name.substring(0, 1); //extract first letter of entity name
return pan_5 == name_1;
}
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
ISVALIDPAN = True
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
Else
name_1 = Strings.Left(name, 1) 'extract first letter of entity name
End If
ISVALIDPAN = (pan_5 = name_1)
End Function
/**
* Structurally validates if specified PAN is correct or not
* @param {string} pan PAN
* @param {string} name Name of entity (optional)
* @return TRUE/FALSE
* @customfunction
*/
function ISVALIDPAN(pan, name = '') {
if (!/^[A-Z]{3}([ABCFGHLJPT])[A-Z][0-9]{4}[A-Z]$/g.test(pan))
return false;
if (!name) //No name found, treat it as optional and skip validation
return true;
let pan_4 = pan.substring(3, 4);
let pan_5 = pan.substring(4, 5);
let name_1 = '';
name = name.toUpperCase();
if (pan_4 == 'P') { //Individual
let parts = name.trim().split(' '); //split name into parts
name_1 = parts[parts.length - 1].substring(0, 1); //extract first letter of surname
}
else //Other name individual
name_1 = name.substring(0, 1); //extract first letter of entity name
return pan_5 == name_1;
}
Variation 1: Pure validation of PAN number structurally
Variation 2: Extended validation with entity name (optional)
Function runs through following checks (as shown in image)
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
- In Excel press Alt + F11 which will open VBA Macro editor
- Go to Tools > References which will open VBA Project References dialog
-
Locate & Tick Microsoft VBScript Regular Expression 5.5 as shown in below image
- Click OK & close VBA Window
