Content authored by practitioners with experience at Amazon India, Intuit QuickBooks, and the Tata Group. Meet the team →
Every Indian finance team that reconciles a corporate bank statement in Excel spends the first working hour of every day converting bank narration strings into a UTR column, a VPA column, a NACH UMRN column, a cheque number column, and a counterparty column that the ERP receipts and payments register can actually match against. Bank narration in India is not standardised. HDFC, ICICI, SBI, Axis, and Kotak each ship their corporate net-banking downloads with different column layouts and different narration prefixes for the same underlying instrument type. Layer in the MT940 SWIFT tag conventions that vary bank by bank, the CSV column-header shifts that follow every net-banking product upgrade, and the auto-truncation of counterparty names at 25 to 40 characters, and the pattern set the finance team has to parse against runs to roughly 300 distinct variants across the five major banks. A single Excel formula cannot handle all of them. What the team needs is a per-bank recipe with a bank-selector dropdown that runs the six narration structures — NEFT, RTGS, IMPS, UPI, NACH, cheque — through purpose-built extraction formulas and produces a match-ready transaction table.
Build the workbook around a raw-input sheet with a bank-selector dropdown at the top and a parsed-output sheet whose formulas key off the selector. The bank selector picks the correct narration-prefix set for HDFC, ICICI, SBI, Axis, or Kotak Corporate Internet Banking, or for the MT940 SWIFT auto-download format. A per-row instrument classifier reads the narration prefix and assigns one of six instrument types — NEFT, RTGS, IMPS, UPI, NACH, cheque. A per-instrument extraction formula then peels off the primary anchor: the UTR for NEFT and RTGS, the IMPS reference for IMPS, the VPA and UPI-TRN for UPI, the UMRN for NACH, and the cheque number for cheque. Every extraction is wrapped in an IFERROR trap that routes unrecognised patterns to Bucket 5 rather than silently dropping the row. A counterparty column is derived from the narration through a separate SUBSTITUTE-and-TRIM cleanup that strips the prefix noise. The workbook uses TEXTBEFORE and TEXTAFTER for delimiter-based extraction on Excel 2021 and newer, and falls back to MID with FIND on older versions. XLOOKUP handles the counterparty-code-to-name master lookup for bulk vendors and customers, SUMPRODUCT drives the exception queue counters, and DATEDIF handles the value-date-versus-transaction-date reconciliation where the bank posts on a T+1 basis.
One raw-input sheet with a bank-selector dropdown covering HDFC, ICICI, SBI, Axis, Kotak, and MT940. A per-bank narration-prefix reference sheet with rows for NEFT-CR, NEFT-DR, RTGS-CR, RTGS-DR, INF/NEFT/, MMT/IMPS/, IMPS-CR, UPI-CR, UPI-DR, NACH/, and CHQ under each bank column. A counterparty master sheet keyed by supplier or customer name variant with the canonical GSTIN or PAN. A configuration cell for the amount tolerance in rupees (typically 1 rupee absorbable, 100 rupees flagged for TDS or MDR investigation). A configuration cell for the reconciliation date used by the T+1 value-date logic. Named ranges on every input column so the extraction formulas reference stable names rather than volatile cell references. Conditional formatting rules that colour-band each row by extracted instrument type so the analyst can visually scan the parsed table before running the composite-key match against the ERP.
A parsed-output sheet with one row per bank statement line, carrying the transaction date, value date, instrument type, extracted UTR or UMRN or UPI-TRN or cheque number, extracted counterparty, cleaned narration, amount, debit or credit flag, and a composite match key ready for XLOOKUP against the ERP. An unparsed exception queue at the top of the driver sheet with a running count and a percentage of daily volume, flagging any run above 2 per cent for a bank-selector or prefix-pattern review. A per-instrument summary showing daily volume and value split across NEFT, RTGS, IMPS, UPI, NACH, and cheque, which the AR analyst reads before triggering the composite-key match. The output sheet is the first sheet the wider bank reconciliation runbook expects on Day 1 morning — every downstream categorisation, aging queue, and exception report on Days 2 to 5 keys off the parsed table this workbook produces.
Every Indian finance team that reconciles a corporate bank statement in Excel spends the first working hour of every day doing the same repetitive parsing job. The daily statement download arrives as a CSV with a Description column carrying narration strings like NEFT-CR-HDFC0000123-N027261234567890-ACME INDUSTRIES PVT or UPI-CR-vendor.acme@hdfcbank-4728362917-Sep bill payment, and the analyst manually splits each string into a UTR column, a VPA column, a UMRN column, a cheque number column, and a counterparty column that the ERP receipts and payments register can actually match against. This is the recipe for a working Excel workbook that runs the bank narration parsing Excel formulas India NEFT RTGS UPI NACH finance teams need: a bank-selector dropdown, six per-instrument extraction formulas built from public XLOOKUP, TEXTBEFORE, TEXTAFTER, MID, and IFERROR primitives, an unparsed exception queue that never silently drops rows, and a parsed output ready for the composite-key match the bank reconciliation runbook walks on Day 1 of the monthly close.
The recipe pairs with the Day 1 to Day 5 bank runbook — the runbook is what happens after the parsed table exists; this workbook is what produces the parsed table. Both fit under the monthly close Playbook that sequences the five reconciliation windows across the twenty-day cadence.
Why Indian bank narration parsing needs its own recipe
Bank narration in India is not standardised. HDFC Corporate Net Banking, ICICI iBusiness, SBI Corporate Internet Banking, Axis Corporate Internet Banking, and Kotak Corporate Internet Banking each ship the daily statement download in a distinct CSV column layout with distinct narration prefixes for the same underlying instrument. The bank statement narration patterns article catalogues the variants — a single NEFT credit surfaces as NEFT-CR-HDFC0000123-N027261234567890-COUNTERPARTY on an HDFC statement, INF/NEFT/N027261234567890/COUNTERPARTY on ICICI, NEFT-N027261234567890 COUNTERPARTY LTD on SBI with the UTR in a separate Ref No column, and either of two more patterns on Axis and Kotak depending on the net-banking product tier.
Layered on top of the CSV narration variants are the MT940 SWIFT statement variants that appear in the auto-download files banks publish to corporate treasury workstations. The MT940 bank statement article covers the SWIFT :86: tag that carries the narration line in a machine-readable format, with each bank encoding the transaction reference, counterparty name, and remittance advice into the tag using its own conventions rather than a global standard.
The result is roughly 300 distinct column-name and narration-prefix variants across the five major Indian corporate banks alone, and the count grows every quarter as banks upgrade net-banking product tiers and shift auto-populated fields. A single Excel formula cannot handle all of them. A per-bank recipe with a bank-selector dropdown that picks the correct prefix pattern set for the current bank is the working shape.
The six narration structures
The workbook handles the six instrument types that account for effectively all corporate bank statement entries in India. Each structure carries a distinct primary anchor that the parsing formula extracts into its own column for the composite-key match downstream.
NEFT — 16-character UTR
Every NEFT credit or debit carries a Unique Transaction Reference issued by the originating bank. The UTR is a 16-character alphanumeric string of the form IIIIYYMMDDNNNNNN — a 4-character IFSC prefix, a 6-character YYMMDD date, and a 6-character per-day sequence. The narration prefix varies by bank: NEFT-CR or NEFT-DR on HDFC and SBI, INF/NEFT/ on ICICI, NEFT- on Axis, and NEFT/CR/ on Kotak.
RTGS — 22-character UTR
RTGS carries a longer 22-character UTR with the same date-and-sequence structure. Narration prefixes are RTGS-CR or RTGS-DR on HDFC and SBI, INF/RTGS/ on ICICI, RTGS- on Axis, and RTGS/CR/ on Kotak.
IMPS — 12-character NPCI reference
IMPS carries a 12-character reference issued by NPCI at the switch. Narration prefixes are MMT/IMPS/ on ICICI and Axis, IMPS-CR or IMPS-DR on HDFC, IMPS- on SBI, and IMPS/P2A/ for person-to-account credits on Kotak.
UPI — VPA plus UPI-TRN
UPI carries a Virtual Payment Address in the form handle@bank alongside a 12-character UPI Transaction Reference (UPI-TRN) and a 22-character NPCI-side transaction ID. The VPA is the counterparty anchor and the UPI-TRN is the match key. Narration prefixes are UPI-CR or UPI-DR on HDFC and SBI, UPI/ on ICICI, and UPI- on Axis and Kotak.
NACH — 20-character UMRN
Every NACH debit mandate carries a Unique Mandate Reference Number (UMRN), a 20-character alphanumeric string allotted by the sponsor bank at mandate registration that stays constant across every subsequent NACH debit on the mandate. Narration prefixes are NACH-DR or NACH/ on all five major banks with the bank-side batch reference in a trailing segment.
Cheque — 6-digit cheque number
Cheque entries carry a 6-digit cheque number and either an in-house Presentment Reference or an OCR-read MICR line. Narration prefixes are CHQ for issued cheques, CHQ DEP for deposited cheques, and CTS for cheque truncation system entries on all five banks.
The parsing recipe — one formula per structure
The formulas below are entirely standard Excel — no add-in, no Power Query, no macro. Excel 2021 and Microsoft 365 versions carry TEXTBEFORE and TEXTAFTER natively; older versions substitute MID with FIND for the same job.
UTR extraction — NEFT and RTGS
=IFERROR(TEXTBEFORE(TEXTAFTER(narration_cell, prefix_cell), "-"), "UNPARSED")The prefix_cell reads from the per-bank reference sheet based on the bank-selector dropdown. For an HDFC NEFT credit, the prefix is NEFT-CR-HDFC0000123-, and the formula peels the 16-character UTR that follows. For ICICI, the prefix is INF/NEFT/. For SBI, the UTR is often in the separate Ref No column and the parsing formula points at that column directly rather than at the Description.
UPI VPA extraction
=IFERROR(TEXTBEFORE(TEXTAFTER(narration_cell, "-"), "-"), "UNPARSED")For a narration like UPI-CR-vendor.acme@hdfcbank-472836291712345-Sep bill payment, the formula returns vendor.acme@hdfcbank. The VPA carries the counterparty domain, which is what the counterparty-master XLOOKUP resolves to a customer or vendor name.
UPI transaction reference extraction
=IFERROR(TEXTBEFORE(TEXTAFTER(narration_cell, vpa_cell & "-"), "-"), "UNPARSED")Reads the 12-character UPI-TRN that follows the VPA segment. This is the primary match key against the ERP UPI receipts register.
NACH UMRN extraction
=IFERROR(TEXTBEFORE(TEXTAFTER(narration_cell, "NACH/"), "-"), "UNPARSED")Peels the 20-character UMRN that follows the NACH prefix. The UMRN keys against the recurring-collection mandate register maintained per customer.
Cheque number extraction
=IFERROR(TEXTBEFORE(TEXTAFTER(narration_cell, "CHQ "), " "), "UNPARSED")Peels the 6-digit cheque number. For CTS-cleared cheques on the same day the prefix is CTS rather than CHQ , and the bank-selector reference sheet holds both.
Counterparty cleanup
=TRIM(SUBSTITUTE(SUBSTITUTE(narration_cell, prefix_cell, ""), utr_cell, ""))Strips the prefix noise and the extracted UTR from the narration to leave the counterparty name segment. A follow-up XLOOKUP against the counterparty master converts the free-text name variant to the canonical vendor or customer code.
Column-truncation detection
=IF(LEN(narration_cell) = max_len_cell, "TRUNCATED", "OK")Where max_len_cell holds the CSV column width the bank exports at (typically 40 characters for the SBI Description column, 25 for older Axis exports). A row flagged TRUNCATED may have lost the counterparty name segment entirely and requires manual read from the corporate net-banking portal.
Error handling — the unparsed trap
Every extraction formula is wrapped in IFERROR that returns UNPARSED into the primary-anchor column when the prefix pattern does not match. Unparsed rows are surfaced as a separate exception queue at the top of the driver sheet with a running count computed via SUMPRODUCT((primary_anchor_column="UNPARSED")*1) and a running percentage against the daily volume.
A count above 2 per cent of the daily volume triggers a review of the bank-selector setting and the extraction prefix set. The trigger fires the same day, not at the end of the week, because a shift in the bank’s narration format tends to affect a full day’s parsing at once rather than a single row. The workbook logs every unparsed row with a timestamp and the bank-selector setting at the time of parsing, so the next day’s pattern set can be extended from the log rather than by guesswork.
The UNPARSED row is never silently dropped and never receives a null value in the primary-anchor column. A silent null would break the composite-key match downstream and pull the row into the wrong categorisation bucket — the failure mode the wider bank cluster catalogues as an amount-matched, key-broken false positive.
The five categorisation buckets
The parsed transaction table becomes the input to the same five-bucket categorisation the wider reconciliation cluster uses across the GST, TDS, and bank windows.
- Bucket 1 — Matched. Composite key (UTR/UMRN/UPI-TRN/cheque number + amount within tolerance + counterparty match) resolves to exactly one ERP receipt or payment. Colour-band green.
- Bucket 2 — Bank-only. Bank statement row with no matching ERP entry. Surfaces missed AP or AR bookings, and the class of platform-settlement receipts where the ERP has booked the gross invoice but not the net-of-MDR bank credit — see the bank charges reconciliation article for the Razorpay 2 per cent MDR plus 18 per cent GST, PayU, and Cashfree settlement patterns.
- Bucket 3 — ERP-only. ERP entry with no matching bank statement row. Cheques issued but not presented, RTGS instructions that failed at the RBI end, NACH mandates that returned unpaid.
- Bucket 4 — Amount mismatch. Row that matches on UTR or UMRN but where the bank amount and the ERP amount are outside the tolerance band. Sub-populations: TDS-net receipts under Section 194Q (payment code 1031, TDS at 0.1 per cent above the Rs 50 lakh single-buyer threshold), Section 194O (payment code 1011, e-commerce operator 1 per cent), Section 194C (payment code 1002), Section 194J (payment code 1005), and Section 194H (payment code 1015); MDR-net platform settlements; retention-money-net project receipts (typically 5 to 10 per cent held back on infrastructure projects).
- Bucket 5 — Unparsed. The exception queue described above, forwarded to the exception categorisation review that closes the daily reconciliation cadence.
The five-bucket vocabulary matches the vocabulary used in the Playbook pillar and across the GST and TDS windows. A single vocabulary across all four reconciliation streams is deliberate — the finance team’s review cadence, escalation ladder, and CARO 2020 clause (xii) audit working paper all read one categorisation set.
Bank-specific patterns — the reference sheet
The reference sheet is a two-dimensional grid with instrument type on the rows and bank name on the columns, and the cell value carries the exact prefix pattern to feed the extraction formula. A sample block:
- HDFC row for NEFT-CR: prefix
NEFT-CR-, followed by 11-character IFSC and 16-character UTR. Read the HDFC Bank reconciliation article for the full column set the HDFC Corporate Net Banking download ships. - ICICI row for NEFT-CR: prefix
INF/NEFT/, followed by 16-character UTR then counterparty. The ICICI Bank reconciliation article covers the iBusiness download layout. - SBI row for NEFT-CR: UTR in the separate
Ref NoCSV column, prefixNEFT-in the Description column, counterparty truncated at 40 characters. - Axis row for NEFT-CR: UTR in the
Cheque/Ref Nocolumn, Description carries a free-text remittance note that varies by the originating bank. - Kotak row for NEFT-CR: prefix
NEFT/CR/in the Description column, UTR embedded after the third slash.
The bank-selector dropdown at the top of the input sheet reads a named range bank_selection and every extraction formula points at INDEX(prefix_grid, MATCH(instrument_type, instrument_column, 0), MATCH(bank_selection, bank_row, 0)) to pull the right prefix. Adding a sixth bank means adding a column to the grid and populating the six instrument rows for that bank — no formula changes anywhere downstream.
The parsed output — one row per statement line
The final output sheet carries one row per bank statement line with columns for transaction date, value date, instrument type, extracted primary anchor, extracted counterparty, cleaned narration, amount, debit or credit flag, bank name, and a composite match key. The composite match key concatenates the primary anchor, the amount rounded to two decimals, and the value date as an Excel serial via =primary_anchor & "|" & TEXT(ROUND(amount,2),"0.00") & "|" & TEXT(value_date,"yyyymmdd") — the pipe delimiter is chosen because it never appears inside any Indian bank narration prefix.
This is the sheet the Day 1 to Day 5 bank runbook expects on the analyst’s screen at 10am on Day 1. Every downstream categorisation, aging queue, and exception report on Days 2 to 5 keys off this table.
The downloadable workbook
The downloadable .xlsx version of this workbook — with the bank-selector dropdown, the reference grid pre-populated for HDFC, ICICI, SBI, Axis, Kotak, and MT940, an unparsed exception queue, a per-instrument daily summary, and named ranges wired throughout — is at /resources/bank-narration-parser-workbook/. The download is watermarked as illustrative and carries a sample HDFC daily statement paste so the recipe can be tested end-to-end before the finance team runs the first real-day parse against it.
When Excel outgrows the workbook
The workbook works cleanly below roughly 500 bank statement lines per working day across the enterprise, one to three bank relationships, and a stable set of net-banking product tiers. Within those bounds a senior AR analyst on Day 1 of the bank window can paste the daily statements, run the parsing, walk the five categorisation buckets, and close the parsing step inside a two-hour block. Above 500 lines per day the parsing step alone consumes the analyst window and pushes the categorisation into the following day, breaking the Day 1 to Day 5 cadence.
Above four bank relationships the bank-selector dropdown pattern breaks down because the analyst has to run the parsing repeatedly against different prefix sets for the same working day. Above roughly 2,000 UPI transactions per day — a threshold most direct-to-consumer businesses cross the day they launch a UPI collection channel — the VPA-based counterparty resolution requires a maintained handle-to-customer master that Excel is a poor fit to hold. And where the bank narration format shifts more than once per quarter, the maintenance overhead on the prefix pattern set consumes more analyst time than the parsing itself.
Above these thresholds the parsing step needs to run on continuously-refreshed reconciliation software for India rather than a daily workbook paste. The manual workbook keeps its role as the reference discipline the platform runs against, not the mechanism the finance team runs by hand — the same relationship the wider Playbook cluster carries between its manual recipes and the TransactIG continuous-reconciliation surface.
Where this fits
- The reconciliation Playbook — monthly close pillar
- Bank reconciliation runbook — Days 1 to 5
- Bank statement narration patterns in India
- Reconciliation software India — pillar guide
Related reading
- HDFC Bank reconciliation
- ICICI Bank reconciliation
- MT940 SWIFT bank statement format
- Bank charges reconciliation — MDR, commission, and net receipts
- CARO 2020 bank reconciliation audit requirements
- Three-way ITC reconciliation Excel workbook — sibling recipe
Content authored by practitioners with experience at Amazon India, Intuit QuickBooks, and the Tata Group. Meet the team →
- ▸ NEFT and RTGS system rules, Reserve Bank of India — The Reserve Bank of India NEFT Procedural Guidelines and the RTGS System Regulations govern the Unique Transaction Reference (UTR) structure that every NEFT and RTGS remittance carries. The UTR is a 16-character alphanumeric string for NEFT and a 22-character string for RTGS, with the first four characters identifying the originating bank IFSC prefix, the next six or eight characters encoding the transaction date in YYMMDD or YYYYMMDD, and the trailing characters carrying a per-day sequence number allotted by the originating branch. Every bank statement narration line for an NEFT or RTGS credit or debit must carry this UTR verbatim, which is what makes it the primary anchor for the match key the parsing recipe below builds.
- ▸ UPI Technical Specifications, National Payments Corporation of India — The UPI Procedural Guidelines and Application Programming Interface specifications require every UPI transaction to carry a Virtual Payment Address (VPA) for both the payer and the payee in the form handle@bank, a 12-character UPI Transaction Reference (UPI-TRN), and a 22-character NPCI-side transaction ID. Bank statement narrations for UPI credits and debits carry a combination of these fields, and the parsing recipe below extracts each of them into separate columns for the composite match key against the ERP receipts and payments register.
- ▸ NACH Operating Guidelines, National Payments Corporation of India — The NACH Operating Guidelines govern the Unique Mandate Reference Number (UMRN) that every NACH debit mandate carries. The UMRN is a 20-character alphanumeric string allotted by the sponsor bank at mandate registration and stays constant across every subsequent NACH debit on the mandate. Every NACH batch also carries a bank-side batch reference and an NPCI-side settlement reference, both of which appear in the corporate bank statement narration. The parsing recipe extracts the UMRN as the primary anchor for match against the recurring-collection mandate register.
- ▸ MT940 SWIFT Statement Message, ISO 20022 and SWIFT standards — MT940 is the SWIFT customer statement message format used by most Indian corporate banks for machine-readable bank statement delivery. The narration field is the SWIFT :86: tag, which carries free-form text up to 6 lines of 65 characters each. Corporate banks encode the transaction reference, counterparty name, and remittance advice into this field using bank-specific conventions rather than a global standard, which is why bank narration parsing in India cannot rely on a single format and needs a per-bank recipe. The parsing recipe below handles the five most common Indian bank conventions.
- ▸ Companies (Auditor's Report) Order 2020, Ministry of Corporate Affairs — CARO 2020 clause (xii) requires the statutory auditor to report on whether the entity has maintained proper records of receipts and payments and whether the bank reconciliation statement has been prepared for each bank account at each reporting date. The audit trail requirement effective 1 April 2023 further requires every accounting software used by the entity to maintain an unalterable log of transactions. A bank narration parsing workbook that generates the match key is a control the auditor will inspect against both requirements — the retention rule under Section 44AA of the Income Tax Act sets the record period at 6 years from the end of the relevant assessment year.