Content authored by practitioners with experience at Amazon India, Intuit QuickBooks, and the Tata Group. Meet the team →
Tally and bank exports fail to match because amounts, dates and keys are stored in different, often text, formats.
Strip title and page-header rows, convert Dr and Cr amounts into separate numeric debit and credit columns, turn text dates into real dates, and trim keys, all in formula columns beside an untouched raw sheet.
A raw sheet holding the export as downloaded, a clean sheet with formula columns using TRIM, SUBSTITUTE, VALUE and DATE, and an ISNUMBER check column.
Two files with the same shape and data types, ready for lookups, with far fewer false unmatched lines.
Before you match anything, give both files the same shape. That means one header row, one transaction per row, real dates, numbers stored as numbers, separate debit and credit columns, and clean keys with no stray spaces. Most failed matches are clean-up failures, not missing entries.
Every formula below works in Excel 2010 and later, including Microsoft 365.
What goes wrong in Tally and bank exports?
| Problem | Where you see it | How to spot it | Fix |
|---|---|---|---|
| Dr or Cr suffix on amounts | Tally ledger exports: “12,500.00 Dr” | Amount is left-aligned | Split sign from number (formula below) |
| Merged title rows | Company name and period above the headers | Filters do not work | Delete rows above the header on the copy |
| Repeated page headers | Bank PDFs converted to Excel | ”Date Narration Debit” appears mid-sheet | Filter the Date column for the word “Date” and delete |
| Dates stored as text | ”05/04/2026” left-aligned | =ISNUMBER(A2) returns FALSE | DATE with LEFT, MID, RIGHT |
| Amounts with commas as text | ”1,25,000.00” will not sum | SUM gives 0 | VALUE with SUBSTITUTE |
| Narration split over rows | Second line has no date or amount | Blank date cells | Join to the row above, then delete |
| Trailing or hidden spaces | Keys that look equal do not match | =LEN(B2) longer than expected | TRIM, and SUBSTITUTE for CHAR(160) |
How do I remove Dr and Cr and convert text to numbers?
Say the Tally amount is in column C, as text like “12,500.00 Dr”.
Debit column:
=IF(RIGHT(TRIM(C2),2)="Dr", VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(C2),"Dr",""),",","")), 0)Credit column:
=IF(RIGHT(TRIM(C2),2)="Cr", VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(C2),"Cr",""),",","")), 0)For “12,500.00 Dr” the debit formula removes “Dr” and the comma, leaving “12500.00 ”, and VALUE turns that into the number 12500. The credit formula returns 0.
For a bank amount that is just text with commas, such as “1,25,000.00”:
=VALUE(SUBSTITUTE(TRIM(D2),",",""))Text dates in dd/mm/yyyy form, such as “05/04/2026” in column A:
=DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2))That returns 5 April 2026 as a real date. It needs the text to be exactly ten characters; check with =LEN(A2)=10.
Keys such as cheque numbers or narrations copied from a web page often carry a non-breaking space that TRIM does not remove:
=TRIM(SUBSTITUTE(B2,CHAR(160)," "))No formulas option: select the column, go to Data, Text to Columns, choose Delimited, click Next twice, set the column data format to Date with DMY, and Finish. Excel converts text dates in place. Do this only on the copy.
Worked example: a 1,250-line statement
An accountant downloads a quarter’s current account statement: 1,250 lines. She tries to match it against the Tally bank ledger and gets 240 unmatched lines.
She checks the data types first:
- =ISNUMBER on the date column: 38 rows return FALSE. They are text dates from one page of the converted PDF.
- =ISNUMBER on the amount column: 112 rows return FALSE. They carry commas and a trailing space.
- The Tally side shows “Dr” and “Cr” on every amount.
She adds the clean-up columns above, then re-runs the match on the clean columns. Unmatched lines fall from 240 to 60. Those 60 are real reconciling items.
| Real reconciling item | Lines | Amount |
|---|---|---|
| Cheques issued, not yet presented | 41 | ₹6,84,300 |
| Deposits in Tally, not yet credited | 9 | ₹2,15,000 |
| Bank charges and GST on charges not booked | 8 | ₹4,720 |
| UPI receipts missing from Tally | 2 | ₹18,500 |
| Total | 60 | ₹9,22,520 |
Check: 41 plus 9 plus 8 plus 2 is 60 lines. ₹6,84,300 plus ₹2,15,000 is ₹8,99,300; add ₹4,720 for ₹9,04,020; add ₹18,500 for ₹9,22,520.
The 180 lines that “matched after cleaning” were never differences. They were formatting. A typical example: the bank showed “1,25,000.00 ” as text and Tally showed “1,25,000.00 Dr”; after cleaning, both became the number 125000 and matched.
How do I keep the original untouched and repeat this every month?
- Keep a sheet called Raw and paste each export into it exactly as downloaded.
- Keep a sheet called Clean where every column is a formula pointing at Raw.
- Add a check column, such as =AND(ISNUMBER(E2), ISNUMBER(F2)), and filter for FALSE before you match.
- Next month, clear Raw, paste the new export, and Clean recalculates.
- Save the workbook under the period name, for example “HDFC CA Sep 2026”.
If your exports change layout often, Power Query (Excel 2016 and later) can record these steps once and replay them when you refresh.
What to read next
Next, read how to reconcile a vendor’s statement with your ledger in Excel. Once the file is clean, you can pull the UTR out of the narration with the bank narration parsing workbook. Tally users should also see Tally Prime reconciliation automation and Tally bank reconciliation.
TransactIG reads bank statements and matches each line to your books, with an exception queue and audit-ready exports, so the clean-up step is handled before matching.