Checks if entered GST Number is structurally valid or not


Cell Reference or Text containing GST Number


(Boolean) Validation result as TRUE / FALSE

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
    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
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

script library isvalidgstn example
In the above illustration, last character of GST Number was mistakently written as S instead of 5 (being similar to read)


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

  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