The two things that go wrong most often when a Union Bank of India statement moves from PDF to Excel are dates and numbers. Dates get read month first, and amounts such as 1,23,456.00 stay as text. Fix both, then count dates against narrations to catch rows that did not convert. We have not assumed Union Bank of India's own layout, so match the column letters to your file.
Why do my dates look wrong?
Excel follows your computer's regional setting. If it expects month first, a date like 03/04/2026 becomes 4 March, and a date like 25/04/2026 cannot be a month, so it stays as text. A column that is half real dates and half text is the giveaway: some entries right-aligned, some left-aligned.
In Google Sheets, set the file's locale to India under the spreadsheet settings before pasting. In Excel, when using Text to Columns, set the date column format to DMY on the last screen.
How do I rebuild text dates?
If dates are text in the form 05/04/2026 in A2, this formula builds a true date:
=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))
RIGHT takes the year, MID the month and LEFT the day. If your file uses dashes or month names such as 05-Apr-2026, the position of each piece changes, so look at one cell and adjust. Format the result as a date and compare it with the PDF for three rows.
How do I convert amounts like 1,23,456.00?
The Indian way of grouping digits is not a problem for the numbers themselves, only for how they were pasted. In a helper column enter:
=VALUE(SUBSTITUTE(D2,",",""))
This removes the commas and converts the rest. Copy it down, paste as values over the old column, then format with an Indian-style number format if you wish.
How do I count whether rows were lost?
Dates and narrations should appear in pairs. Compare them with two formulas (assuming dates in A and narrations in B, up to row 500):
- =COUNT($A$2:$A$500) counts real dates.
- =COUNTA($B$2:$B$500) counts non-empty narrations.
If you have 142 narrations but only 138 dates, four rows still have a text date or are continuation lines. Filter for the odd ones and fix them. It does not prove the total is right, but it finds structural trouble quickly.
How do I check that dates sit in the right month?
A date that was swapped will often land in the wrong month, and the eye misses it in a long list. Two quick formulas help once the dates are real:
- =MIN($A$2:$A$500) shows the earliest date; format the cell as a date. It should be the first day you downloaded.
- =MAX($A$2:$A$500) shows the latest; it should not be after the last day of the statement.
To count rows outside the month you expect, for April, use:
=SUMPRODUCT(($A$2:$A$500<>"")*(MONTH($A$2:$A$500)<>4))
The answer should be 0. A positive number tells you how many rows to inspect. If the formula returns an error, some dates are still text.
Does the total add up?
Do a closing check anyway. Say the opening balance is ₹9,800, credits are ₹55,000, debits are ₹47,320. The expected closing balance is 9,800 + 55,000 − 47,320 = ₹17,480. In the sheet:
=9800+SUM($E$2:$E$500)-SUM($D$2:$D$500)
with debits in D and credits in E. If it does not match the statement, check the biggest suspect first: a text amount that SUM silently ignored.
A short checklist
- Set the date order once, before pasting.
- Convert amounts, then test them with ISNUMBER.
- Compare date count with narration count.
- Match the closing balance.
If you do this every month, it helps to let an importer such as SpendixAI read the file and show you the rows to approve.
Save the cleaned file with a name that shows the period and keep the original PDF beside it. When a figure looks odd months later, you can open both and see at once whether the fault was in the bank's file or in your conversion.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


