To extract the PAN from a GST number in Excel, place the GSTIN in A2 and enter =MID(A2,3,10) in another cell. A standard Indian GSTIN has 15 characters, and its PAN occupies characters 3 through 12, so this formula returns the 10-character PAN. It extracts text from the GSTIN; it does not confirm that the GSTIN or PAN is valid.
The steps below are for Microsoft Excel for Microsoft 365 on Windows. The same formula works in Excel for Mac and Excel for the web, although the ribbon layout can differ slightly. Keep the source GSTINs unchanged in a separate column so you can compare the extracted values with the originals.
What You’ll Need
- An Excel workbook containing the GST numbers you want to process.
- A column with one GSTIN per cell, preferably with a header such as GSTIN in row 1.
- An empty column where Excel can place the extracted PANs.
A GSTIN is a 15-character identifier. In its standard structure, the first two characters indicate the state code, the next 10 are the PAN, the 13th character is an entity number, the 14th character is usually Z, and the 15th is a check character. The extraction formula relies on this fixed position. If your source values have spaces, missing characters, or a different format, check those records rather than assuming the result is correct.
Step-by-Step
- Arrange the data. Put the GSTINs in one column, with a heading in the first row and the first GSTIN in row 2. For example, use column A and label cell A1 GSTIN. Keep one GSTIN in each cell; do not combine several identifiers in a single cell. If the data is already in another column, you can use that column in the formula instead of A.
- Add a heading for the result. In B1, enter PAN. This makes it clear that the values in column B are extracted PAN text, rather than complete GSTINs. Check that the cells below the heading are empty or that you are comfortable replacing their contents.
- Enter the extraction formula. With the GSTIN in A2 and the extracted PAN intended for B2, select B2 and enter
=MID(A2,3,10), then press Enter. MID reads part of a text value: the second argument, 3, tells Excel to begin at character three, and the third argument, 10, tells it to return 10 characters. For example, if A2 contains 27AAPFU0939F1ZV, the result in B2 is AAPFU0939F. - Check the first result. Compare the returned PAN with the original GSTIN. In the example, the first two characters are 27, so the PAN starts at the third character. The PAN ends after the next 10 characters, before the final three characters of the GSTIN. If the result is too short or appears to start or end in the wrong place, inspect the source value for extra spaces or missing characters before filling the formula down.
- Fill the formula for the other rows. Select B2 and point to the small square at the bottom-right corner of the cell, called the fill handle. Drag it down to the last row of your list. If column A has a continuous block of data beside it, you can also double-click the fill handle to fill down alongside that block. Check the last few rows afterwards to make sure Excel copied the formula through the complete list.
- Use an optional blank-safe formula if needed. If column A contains empty rows and you want the corresponding output cells to stay blank, use this version in B2 instead. With GSTINs in A2 downward and PAN results in B2 downward, enter
=IF(A2="","",MID(A2,3,10)). It returns an empty result when A2 is empty; otherwise, it extracts the same 10 characters. Fill it down as described above. This prevents a blank source cell from displaying a string of characters or an error-like result if the formula is changed later. - Review the output before sharing it. Spot-check several rows, including the first, middle, and last entries. Confirm that each source GSTIN has 15 characters and that each extracted result has 10. In a worksheet, you can select a result cell and review the formula bar to see the formula. If you need to send only the extracted PANs, consider copying the result column and using Home > Paste > Paste Values in a separate destination. This replaces formulas with their displayed results, so retain a copy of the original workbook first.
The cell references in the example adjust when you fill the formula down: the next row refers to A3, then A4, and so on. You do not need to edit the formula separately for each GSTIN. If your records begin on row 5, for instance, start with the matching source cell and output cell on row 5, then fill downward from there.
Common Mistakes
- Starting at character 2 or 4. The PAN begins at character 3 in a standard GSTIN. Starting at the wrong place shifts every character in the returned value. The formula needs both the correct start position, 3, and the correct length, 10.
- Using the wrong source cell. If the GSTIN is in C2 but the formula refers to A2, Excel will extract from the wrong cell or return an unexpected result. Match the reference to the column that actually contains the GSTIN.
- Including spaces or pasted characters. A space at the beginning counts as a character, so the formula may return a shifted value. Pasted data can also contain nonprinting characters. Inspect suspicious source cells and clean a copy of the data rather than deleting or altering original records without review.
- Assuming extraction proves validity. A 10-character output only shows what was located at the specified position. It does not check the PAN against official records, validate the GSTIN check character, or establish that the identifier belongs to a particular person or business.
- Overwriting the source list. Put the formula in a separate column. Replacing the GSTINs with extracted PANs removes the original identifiers you may need for checking or traceability.
- Forgetting to fill the formula down. A correct formula in B2 does not automatically populate every row in a normal range. Confirm that the result column reaches the same last row as the source data.
Tips
- Format the source and result columns as Text when importing or pasting identifiers. GSTINs and PANs are identifiers, not quantities to calculate. Text formatting also helps preserve the displayed characters consistently.
- Turn the data range into an Excel table with Insert > Table if you will add records regularly. When you enter the extraction formula in a table column, Excel generally copies it through that calculated column and applies it to new table rows.
- For a long list, use filters to review suspicious source values. On Windows, select the header row and choose Data > Filter. Excel for Mac has a Filter command on the Data tab as well; the exact ribbon arrangement may vary. Filter or sort the source data carefully, and avoid separating PAN results from their corresponding GSTINs.
- Keep the original source file and note where the GSTIN list came from. That makes it easier to resolve discrepancies later, particularly if a value was incomplete or changed during import.
- If you use Excel for the web, enter the same formula in the output cell and use the fill handle or copy and paste it down. Excel for Mac also supports MID and the same formula syntax. No Windows-only function is required for this task.
Troubleshooting
The result is shifted by one character. Check the beginning of the source cell for a leading space or an apostrophe-like character that was included in the text. Also confirm that the formula begins at 3. A leading space counts as character one, moving the PAN one position to the right. Compare the original value in the formula bar with what appears in the cell.
The result has fewer than 10 characters. MID cannot return characters that are not present. The GSTIN may be incomplete, truncated, or entered with a different structure. Check the source cell and confirm it contains the full 15-character GSTIN. Do not fill missing characters with guesses; obtain a corrected value from the source.
The formula appears as text instead of calculating. The cell may be formatted as Text, or the entry may have a leading apostrophe. Change the cell format to General, select the formula cell, press F2, and then press Enter to make Excel recalculate the entry. On many Mac keyboards, use Fn+F2 if the function keys are set to control hardware features. If formulas are displayed throughout the worksheet, press Ctrl+` on Windows to toggle Show Formulas; on Mac, the equivalent shortcut is Control+` in many Excel configurations, though keyboard layouts can affect it. You can also check Formulas > Show Formulas.
Some rows work and others do not. Look for blank cells, extra spaces, shorter values, or inconsistent source formatting in the affected rows. Confirm that the formula references the same source column throughout and that it was filled to the final row. If the list was copied from a website or PDF, re-import or clean the affected values in a separate column, then compare the results with the originals.
The result looks right, but you need confirmation. Extraction is not official verification. If the PAN or GSTIN must be relied on for a business process, confirm the identifier with an appropriate authoritative source or your organization’s verification procedure. Excel can isolate the PAN substring, but it cannot establish the current status or ownership of the number.
Frequently Asked Questions
Which characters of a GSTIN are the PAN?
In the standard 15-character Indian GSTIN structure, the PAN is characters 3 through 12, a total of 10 characters. That is why the formula starts at position 3 and returns 10 characters.
Can I extract the PAN from a GSTIN without a formula?
Yes. You can read characters 3 to 12 from each GSTIN manually, but that is slow and easy to get wrong in a long list. A formula is repeatable and can be filled down. For an occasional single record, use the formula in a spare cell and compare its result with the source.
Will the formula work if my GSTIN is in another column?
Yes. Change the reference to the cell containing the GSTIN. For example, if the first GSTIN is in C2, use =MID(C2,3,10) in the output cell on row 2. Fill that formula down the output column. The starting position and length remain the same.
Can I use this in Google Sheets?
MID is available in Google Sheets, and the same formula structure works there. The instructions for filling, formatting, and navigating menus differ from Excel, but the character positions do not change.
Does this formula check whether the PAN is valid?
No. It returns the 10 characters in the PAN position of the GSTIN. It does not confirm that the PAN is authentic, active, correctly assigned, or associated with the GSTIN. Use an appropriate official or organizational verification process when validation is required.
What if the GSTIN contains spaces?
Spaces count as characters and can shift the extracted substring. Remove unintended spaces from a reviewed copy of the source data, then extract again. Do not remove characters automatically from every value without checking, because a space or unusual formatting may signal a broader import problem.
Related
- How to Enable the Share Option in Excel for Collaboration
- How to Enable Merge Cells in Excel
- How to Enable Paste Special in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Table Drop-Down List: Set Up a Dynamic ListTo add a drop-down list to an Excel table, select the cells where users will choose an item,…
- Excel Keyboard Shortcuts to Repeat the Last ActionIn Excel for Microsoft 365 on Windows, press F4 to repeat the last action when Excel can repeat…
- How to Remove Formatting in Word Without Losing TextTo remove formatting in a Word document, select the text, then choose Home > Clear All Formatting. To…
- Google Sheets GOOGLEFINANCE Formulas: Examples and TipsIn Google Sheets, use =GOOGLEFINANCE("NASDAQ:GOOG","price") to fetch the latest available price for Alphabet’s stock, or replace the ticker…
- Excel Compare Two Sheets and Show Differences ClearlyTo compare matching cells on two worksheets, put this formula in a third sheet and fill it across…
- Excel Table Not Pasting Into Outlook? Fixes That WorkIf an Excel table is not pasting into Outlook, copy the cells again, click in the message body,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.