ISVALIDGSTN
Intro
Checks if entered GST Number is structurally valid or not
Syntax
=ISVALIDGSTN(gstn)
Input
gstn(String)
Cell Reference or Text containing GST Number
Output
(Boolean) Validation result as TRUE / FALSE
Code
Target Platform:
Function ISVALIDGSTN(gstn As String) As Boolean
Dim expr As New RegExp
expr.Pattern = "^(0[1-9]|[1-2][0-9]|3[0-7])[A-Z]{3}([ABCFGHLJPT])[A-Z][0-9]{4}[A-Z][1-9][Z][0-9A-Z]$"
If Not expr.Test(gstn) Then
ISVALIDGSTN = False
Exit Function
End If
'Calculate 15th digit checksum from 14 digits and compare it
Dim gstnCodepointChars As String
gstnCodepointChars = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim factor As Integer, sum As Integer, checkCodePoint As Integer, md As Integer
factor = 2
sum = 0
checkCodePoint = 0
md = 36
Dim i As Integer
For i = 14 To 1 Step -1
Dim codePoint As String, c As String
c = Strings.Mid(gstn, i, 1)
codePoint = Application.WorksheetFunction.Search(c, gstnCodepointChars)
codePoint = codePoint - 1
Dim digit As Integer
digit = factor * codePoint
factor = IIf(factor = 2, 1, 2)
digit = Int(digit / md) + (digit Mod md)
sum = sum + digit
Next
checkCodePoint = (md - (sum Mod md)) Mod md
Dim checksumCharacter As String
checksumCharacter = Strings.Mid(gstnCodepointChars, checkCodePoint + 1, 1)
ISVALIDGSTN = (Strings.Right(gstn, 1) = checksumCharacter)
End Function
/**
* Structurally validates specified GST Number
*
* @param {string} gstn GST Number to validate
* @return TRUE/FALSE
* @customfunction
*/
function ISVALIDGSTN(gstn) {
if (!/^(0[1-9]|[1-2][0-9]|3[0-7])[A-Z]{3}([ABCFGHLJPT])[A-Z][0-9]{4}[A-Z][1-9][Z][0-9A-Z]$/g.test(gstn))
return false;
//Calculate 15th digit checksum from 14 digits and compare it
let gstnCodepointChars = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
let factor = 2, sum = 0, checkCodePoint = 0;
let mod = gstnCodepointChars.length;
for (let i = gstn.length - 2; i >= 0; i--) {
let codePoint = gstnCodepointChars.indexOf(gstn.charAt(i));
let digit = factor * codePoint;
factor = factor == 2 ? 1 : 2;
digit = (digit / mod) + (digit % mod);
sum += Math.floor(digit);
}
checkCodePoint = (mod - (sum % mod)) % mod;
let checksumCharacter = gstnCodepointChars.charAt(checkCodePoint);
return gstn.substring(14, 15) == checksumCharacter;
}
Function ISVALIDGSTN(gstn As String) As Boolean
Dim expr As New RegExp
expr.Pattern = "^(0[1-9]|[1-2][0-9]|3[0-7])[A-Z]{3}([ABCFGHLJPT])[A-Z][0-9]{4}[A-Z][1-9][Z][0-9A-Z]$"
If Not expr.Test(gstn) Then
ISVALIDGSTN = False
Exit Function
End If
'Calculate 15th digit checksum from 14 digits and compare it
Dim gstnCodepointChars As String
gstnCodepointChars = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim factor As Integer, sum As Integer, checkCodePoint As Integer, md As Integer
factor = 2
sum = 0
checkCodePoint = 0
md = 36
Dim i As Integer
For i = 14 To 1 Step -1
Dim codePoint As String, c As String
c = Strings.Mid(gstn, i, 1)
codePoint = Application.WorksheetFunction.Search(c, gstnCodepointChars)
codePoint = codePoint - 1
Dim digit As Integer
digit = factor * codePoint
factor = IIf(factor = 2, 1, 2)
digit = Int(digit / md) + (digit Mod md)
sum = sum + digit
Next
checkCodePoint = (md - (sum Mod md)) Mod md
Dim checksumCharacter As String
checksumCharacter = Strings.Mid(gstnCodepointChars, checkCodePoint + 1, 1)
ISVALIDGSTN = (Strings.Right(gstn, 1) = checksumCharacter)
End Function
/**
* Structurally validates specified GST Number
*
* @param {string} gstn GST Number to validate
* @return TRUE/FALSE
* @customfunction
*/
function ISVALIDGSTN(gstn) {
if (!/^(0[1-9]|[1-2][0-9]|3[0-7])[A-Z]{3}([ABCFGHLJPT])[A-Z][0-9]{4}[A-Z][1-9][Z][0-9A-Z]$/g.test(gstn))
return false;
//Calculate 15th digit checksum from 14 digits and compare it
let gstnCodepointChars = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
let factor = 2, sum = 0, checkCodePoint = 0;
let mod = gstnCodepointChars.length;
for (let i = gstn.length - 2; i >= 0; i--) {
let codePoint = gstnCodepointChars.indexOf(gstn.charAt(i));
let digit = factor * codePoint;
factor = factor == 2 ? 1 : 2;
digit = (digit / mod) + (digit % mod);
sum += Math.floor(digit);
}
checkCodePoint = (mod - (sum % mod)) % mod;
let checksumCharacter = gstnCodepointChars.charAt(checkCodePoint);
return gstn.substring(14, 15) == checksumCharacter;
}
Example
In the above illustration, last character of GST Number was mistakently written as S instead of 5 (being similar to read)
Validations
Following checks (or validations) are applied on GST Number
- First 2 characters i.e. State Code must be between 01 to 37
- Next 10 characters i.e. PAN is validated as illustrated in ISVALIDPAN function
- 13th character must be between 0 to 9
- 14th character must be Z
- Checksum is calculated based on 14 characters & is matched with 15th character of GST Number
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
- 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
