Clean up a bank statement CSV before you import it: dates, amounts, encoding and duplicates

Published 17 October 2026 · by Tekiba

Before you start: a copy, opened the right way

Keep the file from the bank untouched and work on a copy. Every fix below changes data, and one of them deletes rows.

How you open the copy matters. Microsoft: “When Excel opens a .csv file, it uses the current default data format settings to interpret how to import each column of data.” That is when dates get rewritten and leading zeros disappear. Open it instead with Data › From Text/CSV, which lets you choose the separator and the encoding and see a preview first. In Google Sheets, use File › Import; for a .csv you can pick the “Separator character” or let it detect one.

1. Dates: one format, and nothing else in the cell

Intuit's first rule for dates is simple: “Make sure the dates are in one format.” A file that mixes 03/04/2026 with 2026-04-05, or day-first with month-first, will be read wrong somewhere. If the spreadsheet already converted them when it opened the file, why Excel changes the dates in your bank CSV shows how to get the original text back.

Some banks add more than the date: “Some banks add the day of the week in the Date column (for example, 20/11/2018 TUE). You need to remove the day of the week.” Intuit's fix is Data › Text to Columns, split on a space, so the weekday lands in a column of its own that you then delete. Google Sheets has the same under Data › Split text to columns. Insert an empty column to the right of Date first: the split writes the weekday into the next column, and without the empty one it would write over your descriptions. Check that the descriptions are intact before deleting the weekday column.

Then check the year. A statement that prints dates without one leaves the converter to work it out, and December–January statements are where that goes wrong: see why your converted bank CSV shows the wrong year.

2. Amounts: plain numbers, no thousands separators or currency signs

An amount written $1,250.00 or 1.250,00 is text to an importer. It needs to become 1250.00.

In Excel, NUMBERVALUE does it in one step. Microsoft describes it as converting “text to a number, in a locale-independent way”, with the decimal and group separators given as arguments, and its own example is =NUMBERVALUE("2.500,27",",","."), which returns 2500.27. For a dollar figure, take the sign off first: =NUMBERVALUE(SUBSTITUTE(C2,"$",""),".",",").

In Google Sheets, remove the characters with SUBSTITUTE and convert the result with VALUE. Google's note on SUBSTITUTE: “This function returns text as the output. If a number is desired, try using the VALUE function in conjunction with this function.” So =VALUE(SUBSTITUTE(SUBSTITUTE(C2,"$",""),",","")) for a figure written with a point for the decimal.

Watch for negatives that are not written with a minus. (1,250.00) and 1,250.00- both mean money out on a statement; neither formula above reads them as negative. Find them before converting, and if a column has many, why “PDF to Excel” makes bank statements messy explains where they come from.

3. Signs: one column, money in positive

Most importers want one Amount column, positive for money in and negative for money out. Bank downloads often have two, Withdrawals and Deposits. Do step 2 on both columns first, so every figure in them is a number. That order matters: N turns anything that is not a number into 0, and that includes a figure stored as text, so a withdrawal still written "1,250.00" would come out as nothing. Once both columns are numbers, add a column with =N(D2)-N(C2), where D is deposits and C is withdrawals; N also turns an empty cell into 0, so either side may be blank. Fill it down and check that its total equals total deposits minus total withdrawals. Only then paste it back as values and delete the two old columns.

QuickBooks Online's four-column layout goes the other way: it keeps two columns, and its labels are not what an accountant would expect. Should debits be negative in a bank CSV? sets out who wants which.

4. Encoding: save it as UTF-8

If a payee such as Café Rio shows up as Café Rio, the file was read in the wrong encoding. Microsoft's advice for CSV files is plain: “For best results, make sure that the CSV file has UTF-8 encoding. This encoding works for all languages and alphabets.” Its steps for converting a file in Excel are to open it with Data › From Text/CSV, and then “under File Origin, choose the encoding that makes the characters in the text appear correctly”. When they read correctly, “select CSV UTF-8 (Comma delimited) (*.csv) as your file type” in Save As.

Fixing the encoding does not make every character acceptable. Some importers refuse certain symbols in descriptions whatever the encoding; the errors guide lists what QuickBooks says about them.

5. Duplicates: find them before you delete them

A row can be in a file twice for two reasons. Statements that overlap put the same transactions in twice, and those copies should go. But two coffees for the same price on the same day are two real payments, and those must stay.

The spreadsheet tools cannot tell the difference. Microsoft: “When duplicates are removed, the first occurrence of the value in the list is kept, but other identical values are deleted.” The comparison is only on the columns you tick, and it is on what is shown: it “depends on what appears in the cell—not the underlying value stored in the cell.” Google Sheets is broader still: “Cells with identical values but different letter cases, formatting, or formulas are considered to be duplicates.”

So look before you delete. Microsoft's own advice: “Before removing duplicate values, it's a good idea to first try to filter on—or conditionally format on—unique values to confirm that you achieve the results you expect.” In Excel, Home › Conditional Formatting › Highlight Cells Rules › Duplicate Values colours them. Then use the balance: if the file has a running Balance column, include it in the comparison. Two genuine payments of the same amount leave different balances behind them; a row copied in from an overlapping statement has the same balance as its twin. Where the files came from different months, merge 12 months of bank statements into one CSV shows how to find where they overlap.

Save, then check the totals

Save as CSV, and open the saved file in a text editor to see what the importer will see: the dates as you meant them, no currency signs, a point for the decimal. Then do the arithmetic. The opening balance plus every amount should reach the closing balance on the statement. How to check a converted bank statement CSV for errors takes about a minute and catches a row that went missing while you were cleaning.

If the CSV comes from a PDF

A file converted from a PDF statement can be written clean in the first place. LedgerDrop's free CSV already has one date format (YYYY-MM-DD), one signed Amount column with money in positive, plain numbers with a point and two decimal places by default, and UTF-8 text. The free version converts one statement at a time, so where two statements overlap, the rows they share are still yours to find. It reads brackets, a trailing minus and DR as negative, and before you download it checks the rows against the statement's printed balances. On a long statement, the rows either side of each page break are the ones to read: where multi-page statements break lists what goes wrong there.

What this page cannot tell you

The menu names are from Microsoft's and Google's pages as read on one day, and both change their menus between versions. The formulas were written for this page from the functions those pages document; adjust the separators to how your file is written. Whether two identical rows are one payment or two is something only the statement can answer.

Questions