Bank Statement Lesson 2: Clean Text Dates and Amounts

Bank Statement Lesson 2: Clean Text Dates and Amounts featured illustration

πŸ“Ž This article includes 1 downloadable practice file ↓

⏱ 2 min read

πŸ“˜ Bank Statement Automation Course Β· Lesson 2 of 8

In this article
  1. Text dates (dd/mm/yyyy)
  2. Text amounts with commas
  3. One signed Amount column
  4. Common mistakes
  5. Practice
  6. Continue learning

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.

Advertisement

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.

Advertisement
πŸ’‘ Power Query does all of this in clicks and remembers the steps for next month (lesson 7). Formulas are best for understanding and one-off files.

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.

πŸ“Ž Practice files for this article

Free to use for learning. Files with macros (.bas) are plain text β€” import them with Alt+F11 β†’ File β†’ Import File, and always test on a copy.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free Β· AI can be wrong