Yes. If a GSTIN is in cell A2, extract its embedded 10-character PAN with =MID(A2,3,10). The formula takes 10 characters beginning at position 3, which is where the PAN appears in the standard 15-character GSTIN structure.
Why the PAN is inside a GSTIN
A GSTIN is a 15-character identification number used for GST registration in India. Its structure incorporates the PAN associated with the registered person or entity, so you can retrieve the PAN from a correctly formed GSTIN without a separate lookup. This is an extraction, not a process that discovers or verifies a taxpayer’s identity.
The standard structure is commonly described as follows: the first two characters represent a state or union territory code; characters 3 through 12 contain the 10-character PAN; the 13th character indicates the registration number for that PAN within the state or union territory; the 14th character is normally Z; and the final character is a check character. This layout is why the same extraction formula works across GSTINs.
For example, the illustrative string 27ABCDE1234F1Z5 has ABCDE1234F in characters 3 through 12. The example demonstrates the position of the PAN; it should not be treated as proof that the full GSTIN is genuine or that its final check character is valid.
Extract the PAN with MID
In Microsoft Excel for Microsoft 365 on Windows, put the GSTIN in A2 and select an empty cell, such as B2. With the GSTIN in A2, enter this formula in B2:
=MID(A2,3,10)
MID returns a specified number of characters from a text string. In this formula, A2 is the text to read, 3 is the starting position, and 10 is the number of characters to return. For the illustrative GSTIN 27ABCDE1234F1Z5 in A2, the result is ABCDE1234F.
Press Enter to calculate the result. Excel normally displays the returned characters in B2, and the formula remains in the formula bar when B2 is selected. If Excel displays the formula itself rather than the result, check whether the destination cell is formatted as Text or whether Show Formulas is enabled. Changing the cell to General and re-entering the formula usually resolves a text-format issue; the Formulas tab includes the Show Formulas control.
The same formula works in Excel for Mac and in current Excel for the web. Menu placement or keyboard behavior can differ between platforms, but the MID function and its arguments do not change. Use commas between arguments in the formula as shown; Excel may require a different list separator in some regional settings, depending on the system configuration.
Fill the formula down a list
If column A contains many GSTINs, enter the formula once beside the first record, then copy it for the remaining rows. Because the reference A2 is relative, Excel adjusts it automatically: the next row refers to A3, then A4, and so on. This makes one formula suitable for a whole column of records.
- Place the heading GSTIN in A1 and PAN in B1, if the sheet has a header row.
- Enter the first GSTIN in A2 and the extraction formula in B2.
- Drag the small fill handle at the lower-right corner of B2 down alongside the data. Alternatively, select the formula cell and the cells below it, then choose Home > Fill > Down.
- Check a few outputs against the source strings, especially the first and last rows, before using the extracted column elsewhere.
If the list is an Excel table, entering the formula in the first row of the new PAN column may cause Excel to fill the calculated column automatically. Review the filled results rather than assuming every source row is clean. To retain only the extracted values instead of formulas, copy the PAN results and use Paste Special > Values in the destination. Keep the original GSTIN column if you may need to audit or correct the extraction later.
Check entries and handle errors
MID will return characters from whatever text it receives; it does not check that the source is a valid GSTIN. A short entry can therefore produce a shorter result rather than a meaningful warning. If your source list may contain incomplete values, use a guarded formula so that a nonblank entry with a length other than 15 is flagged.
With GSTINs in A2 and the result or warning in B2, use:
=IF(A2=","",IF(LEN(A2)=15,MID(A2,3,10),"Check GSTIN"))
This version leaves B2 blank when A2 is blank, extracts the characters when the entry has 15 characters, and displays Check GSTIN otherwise. For the 15-character illustrative value above, it returns ABCDE1234F. The length test catches missing or extra characters, but it does not validate the GSTIN’s check character, state code, PAN format, registration status, or whether the number belongs to the person claimed.
Look out for common data problems before relying on the results:
- Leading or trailing spaces: These can make the string longer than 15 characters or shift the extraction positions. Inspect suspicious entries and clean the source data carefully.
- Wrong characters: A zero and the letter O, or a one and the letter I, can be confused during manual entry. MID copies the character as supplied; it cannot infer the intended character.
- Extra punctuation: Hyphens, quotation marks, or other separators change character positions. Remove them from the source only when you know they are not part of the underlying value.
- Wrong column reference: If the GSTIN is not in A2, change the reference to the correct cell. A formula can calculate successfully while pointing at the wrong data.
Work with pasted data and formatting
GSTINs contain letters as well as digits, so Excel will generally treat them as text when they are entered normally. Still, when importing from a CSV, a website, or another workbook, check the source and destination columns rather than assuming the values arrived unchanged. If leading or trailing spaces are present, a straightforward MID formula uses the wrong positions. Clean or correct those source entries first, then compare the cleaned value with the original so that the change is transparent.
Do not remove characters just because a row looks unusual. For example, deleting a character to force the length to 15 could conceal a transcription error. A useful review process is to filter the source column for unusual lengths, inspect flagged records against the authoritative source, and document corrections. Excel’s sort and filter tools can help organize the review, but they do not establish whether a GSTIN is authentic.
If you paste the formula into a cell and Excel treats it as plain text, change that cell’s number format to General and enter the formula again. If the output seems to have disappeared, widen the result column or check whether adjacent cells contain values that prevent the text from spilling. The PAN is a text identifier, not a quantity: avoid converting it to a number or applying numeric formatting.
Formula versus manual extraction
For a single GSTIN, counting across the characters by hand can work, but a formula is easier to repeat and less prone to skipped characters. For a list, MID gives every row the same positional rule and updates the result when the source value changes. That is generally more maintainable than manually typing PANs into a separate column.
Excel’s Text to Columns feature is not necessary for this simple task. It can split data when a source uses consistent delimiters, but a GSTIN’s PAN is defined by fixed character positions, not by a separator. A fixed-width split can be configured for more complex workflows, yet the MID formula is usually the clearest method for extracting just these 10 characters. If you need an authoritative validity or registration check, use an appropriate official verification route rather than treating a spreadsheet formula as validation.
Protect PAN data in the workbook
A PAN is sensitive identifying information. Extracting it into a new column does not reduce that sensitivity; it makes the PAN easier to see, copy, filter, and export. Share only the workbook and columns required for the task, store it in an approved location, and follow your organization’s access and retention rules. Avoid sending the sheet to an unapproved conversion website or placing it in a broadly shared folder just to run a simple formula.
Hiding the PAN column is not the same as protecting the data. A recipient may be able to unhide it, and worksheet protection is not a substitute for controlling access to the file. If a report does not need full PANs, consider whether it can use masked or otherwise minimized data instead, following the applicable organizational requirements. Keep an unaltered source copy where appropriate so that corrections and calculations can be checked.
Frequently Asked Questions
Which characters of a GSTIN are the PAN?
Characters 3 through 12, counting from the first character, contain the 10-character PAN in the standard GSTIN structure. The first two characters come before it, and the remaining three characters follow it.
Can I extract the PAN from a GST number in Excel?
Yes. A GST number is commonly called a GSTIN. If it is in A2, =MID(A2,3,10) returns characters 3 through 12. The result is only as reliable as the source value.
Does the formula confirm that a GSTIN is valid?
No. MID extracts text at fixed positions. Even a 15-character result may contain a mistyped PAN or an invalid GSTIN. Use an appropriate verification process when validity or registration status matters.
Why is my extracted PAN too short or incorrect?
Check that the source contains the complete GSTIN, that the formula refers to the right cell, and that spaces or punctuation have not shifted the character positions. Compare the original text with a trusted source rather than guessing at corrections.
Can I use the same formula in Excel for Mac?
Yes. MID uses the same arguments in Excel for Mac, and the formula =MID(A2,3,10) works there as well. The interface and some keyboard shortcuts may differ, but the extraction logic is unchanged.
Related
- Excel Unmerge Cells and Fill Every Cell with Its Data
- Excel Undo Shortcut: Ctrl+Z and How It Works
- Excel UK Number Format: Dates, Currency and Separators
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Group Names in Excel: Sort, Count, or CollapseTo group names in Excel, sort the list by the name or category column; to make rows collapsible,…
- Excel OFFSET Function: How It Works and When to Use ItExcel’s OFFSET function returns a reference to a cell or range that lies a specified number of rows…
- Excel MIN Ignore NA: Formulas That WorkYes. In Excel for Microsoft 365, use =AGGREGATE(5,6,A2:A10) to find the smallest numeric value in A2:A10 while ignoring…
- Excel Formula for Overdue Days: Dates and ExamplesTo calculate overdue days in Excel, put the due date in A2 and use =IF(A2="","",MAX(0,TODAY()-A2)) in another cell.…
- Cash Flow Formula in Excel: Examples and How to Use ItThe basic cash flow formula in Excel is cash inflows minus cash outflows. If inflows are in B2…
- Pull Numbers From Text in Excel: Formulas & MethodsYes. In Excel for Microsoft 365, you can pull every digit from a text cell with a formula,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.