ISVALIDGSTN - Excel Kida (2024)

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:
Excel VBA
Google Spreadsheet

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
• 13^th character must be between 0 to 9
• 14^th character must be Z
• Checksum is calculated based on 14 characters & is matched with 15^th 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

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
   
4. Click OK & close VBA Window