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 BooleanDim expr As New RegExpexpr.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 FunctionEnd If'Calculate 15th digit checksum from 14 digits and compare itDim gstnCodepointChars As StringgstnCodepointChars = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"Dim factor As Integer, sum As Integer, checkCodePoint As Integer, md As Integerfactor = 2sum = 0checkCodePoint = 0md = 36Dim i As IntegerFor 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 + digitNextcheckCodePoint = (md - (sum Mod md)) Mod mdDim checksumCharacter As StringchecksumCharacter = Strings.Mid(gstnCodepointChars, checkCodePoint + 1, 1)ISVALIDGSTN = (Strings.Right(gstn, 1) = checksumCharacter)End Function
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
To write any comments or post queries, kindly sign-in to your Facebook & then refresh this page