Bank Statement Lesson 2: Clean Text Dates and Amounts

📎 This article includes 1 downloadable practice file ↓

📘 Bank Statement Automation Course · Lesson 2 of 8

Most exports look fine but are text underneath: SUM gives 0, dates won’t sort, filters show “04/05/2026” as a word. Here’s how to fix both.

Text dates (dd/mm/yyyy)

=DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2))

This works whatever your Windows date setting is, because you tell Excel which part is which. For a whole column at once: select it > Data > Text to Columns > Next > Next > Date: DMY > Finish.

Text amounts with commas

=VALUE(SUBSTITUTE(C2, ",", ""))

Indian grouping (1,23,456.00) is not a problem once the commas are gone. Some banks add “Cr”/”Dr” after the amount: strip them with another SUBSTITUTE.

One signed Amount column

Withdrawal and Deposit in separate columns are awkward for summaries. Make one column, credits positive and debits negative:

=N(Deposit) - N(Withdrawal)

N() turns blanks into 0. With text amounts, clean them first as above.

Common mistakes

• Green triangles everywhere: numbers stored as text. Select the column > the warning icon > Convert to Number.
• Non-breaking spaces from PDF conversions: VALUE fails until you also SUBSTITUTE CHAR(160).

Practice

Download the workbook below. The Raw sheet is a typical text export. The statement is made up but follows real Indian bank formats (NEFT, NACH, UPI narrations). The Check column turns green when your formula gives the right answer.