How to Fix Common Bank Statement CSV Formatting Issues

Quick Answer {#quick-answer}

The most common bank statement CSV formatting issues are date format errors (Excel converting dates to numbers), character encoding problems (strange characters replacing accented letters), negative amounts displayed as text, duplicate header rows from merged files, and Excel auto-formatting account numbers as scientific notation. QuickBankConvert outputs consistently formatted CSVs that avoid all of these issues — but if you have already-downloaded CSVs with problems, this guide covers how to fix each one.

Issue 1: Date Format Errors {#date-format-issues}

Symptom

Your CSV contains dates like 2024-03-15 or 03/15/2024, but after opening in Excel they appear as numbers (e.g., 45366) or get rearranged to incorrect dates.

Why It Happens

Excel aggressively interprets data when you open a CSV by double-clicking. It reads date-formatted text and converts it to its internal date serial number format. This is normally helpful, but it causes two problems:

1. The display format may not match what you need
2. Date interpretation depends on your system's regional settings — a US system reads 03/15/2024 as March 15, but a European system reads it as March 3 (day/month format)

How to Fix It

Prevention (best approach): Never open a CSV by double-clicking. Instead:

1. Open Excel
2. Go to Data → Get Data → From File → From Text/CSV (Excel 2019+) or Data → From Text (older Excel)
3. In the preview, click the date column header
4. Select Date format and specify the order: YMD, MDY, or DMY

Fix existing numbers: If your dates already converted to serial numbers, select the date column, right-click → Format Cells → Date, and choose your preferred date format. Excel will display the correct date from the serial number.

Fix via formula: If dates are formatted wrong, use Excel's DATEVALUE or TEXT functions:

=TEXT(A2,"YYYY-MM-DD")  ← converts any date to ISO format
=DATEVALUE("03/15/2024")  ← converts text date to serial number
=DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2))  ← manually parse MM/DD/YYYY

Using Google Sheets instead: Google Sheets is less aggressive about auto-formatting. Import your CSV via File → Import → Upload, and specify the date format. Google Sheets usually handles mixed date formats better than Excel.

Callout: The ISO date format solution

Using YYYY-MM-DD (ISO 8601) date format eliminates ambiguity permanently. This format sorts alphabetically in chronological order, is not affected by regional settings, and is unambiguous — January 15 is always 2024-01-15, not 01/15/24 or 15/01/24. QuickBankConvert outputs ISO date format by default.

Issue 2: Character Encoding Problems {#encoding-issues}

Symptom

Your CSV opens with strange characters: é instead of é, ’ instead of an apostrophe, or ? marks where special characters should appear.

Why It Happens

Text files, including CSVs, must be encoded in a specific character set. Common encodings include:

• UTF-8: Universal, supports all languages, the web standard
• UTF-8 BOM: UTF-8 with a Byte Order Mark at the start — sometimes required by Excel
• Windows-1252 (Latin-1): Common in older Windows applications and some US banks
• ISO-8859-1: Similar to Windows-1252, common in European bank exports

When you open a file encoded in Windows-1252 as UTF-8 (or vice versa), special characters — particularly accented letters (é, ü, ñ) and typographic characters (", ', —) — display incorrectly.

How to Fix It

Step 1: Identify the encoding

Open the CSV in a text editor that shows encoding. Notepad++ (Windows), VS Code (all platforms), or TextWrangler (Mac) all display and allow changing file encoding.

Step 2: Convert to UTF-8

In Notepad++:

1. Open the file
2. Go to Encoding → Convert to UTF-8
3. Save the file

In VS Code:

1. Open the file
2. Click the encoding indicator in the bottom-right status bar (usually says "UTF-8" or "Windows 1252")
3. Select Reopen with Encoding to try different encodings until characters display correctly
4. Then Save with Encoding → UTF-8

Step 3: Re-import into Excel

After converting to UTF-8, import the cleaned CSV using Excel's Text Import Wizard with UTF-8 encoding specified.

Prevention

When downloading bank statements directly, look for encoding options in the download dialog. Choose UTF-8 if available. QuickBankConvert always outputs UTF-8 CSV files to prevent encoding issues.

Issue 3: Negative Amounts Not Recognized {#negative-amount-issues}

Symptom

Debits and withdrawals appear as positive numbers, or amounts in parentheses (like (125.00)) are not calculated correctly in sum formulas.

Why It Happens

Banks use various conventions for indicating negative amounts:

• Minus sign prefix: -125.00 — standard, works everywhere
• Parenthetical notation: (125.00) — accounting convention, not recognized as negative by Excel by default
• Separate Debit/Credit columns: A positive number in "Debit" vs "Credit" — requires formula interpretation
• CR/DR suffix: 125.00 CR or 125.00 DR — text format, not numeric

How to Fix It

Fixing parenthetical negatives:

Use Excel's Text to Number conversion with a formula:

=IF(LEFT(A2,1)="(",-(VALUE(MID(A2,2,LEN(A2)-2))),VALUE(A2))

This checks if the amount starts with (, strips the parentheses, and makes the value negative.

Or use Find & Replace:

1. Find: ( Replace with: -
2. Find: ) Replace with: nothing
3. Then convert the column from text to number using Data → Text to Columns

Combining Debit/Credit columns into one:

=IF(B2<>"",B2*-1,C2)

Where B2 is the Debit column and C2 is the Credit column. This creates a signed amount where debits are negative and credits are positive.

Fixing CR/DR text notation:

=IF(RIGHT(A2,2)="DR",-VALUE(LEFT(A2,LEN(A2)-3)),VALUE(LEFT(A2,LEN(A2)-3)))

Issue 4: Duplicate Header Rows {#duplicate-headers}

Symptom

After merging multiple CSV files, you have extra rows in the middle of your data that contain column headers (Date, Description, Amount, etc.) instead of transaction data. Formulas that try to sum the amount column fail because of these text rows.

Why It Happens

When you merge CSVs by copy-paste or use the Windows copy command, the header row of each source file gets included in the output. A merged 12-month file may have 12 header rows scattered throughout.

How to Fix It

Quick Excel fix using Filter:

1. Select your entire dataset
2. Go to Data → Filter
3. Click the filter dropdown on the "Date" column
4. Uncheck "Date" (the literal text) from the filter list
5. Select all visible rows and delete them
6. Clear the filter

Find & Replace approach:

If your header starts with a specific word like "Date":

1. Ctrl+H (Find & Replace)
2. Find: the entire header row text
3. Replace with: nothing
4. This removes exact header matches

Power Query (automated and repeatable):

If you regularly merge CSVs, set up a Power Query workflow that automatically skips header rows from all but the first file. Power Query's "Combine Files" function handles this automatically when loading from a folder.

Issue 5: Excel Auto-Formatting Problems {#excel-auto-format}

Symptom

Account numbers appear as scientific notation (1.23457E+14), long transaction IDs are truncated, dates change to incorrect formats, or ZIP codes lose their leading zeros.

Why It Happens

Excel applies "intelligent" formatting when opening CSV files, interpreting strings that look like numbers as numbers and truncating values that exceed its precision limit (15 significant digits). Account numbers, card numbers, and routing numbers often have more than 15 digits and get corrupted.

How to Fix It

Prevention via Text Import Wizard:

1. Open Excel → Data → From Text/CSV
2. In the column preview, click the column containing account numbers
3. Select Text as the data type for that column (not General or Number)
4. This forces Excel to treat the values as text strings, preserving all digits

Fix existing scientific notation:

1. Right-click the affected column → Format Cells → Number → 0 decimal places
2. The number displays without scientific notation but may be truncated if it exceeded 15 digits — unfortunately, truncated digits cannot be recovered from Excel

For future imports, always use Text Import Wizard and format account number columns as Text.

Preventing date auto-conversion: Same solution — use Text Import Wizard and explicitly set date columns to Text or Date with the correct format.

Issue 6: Wrong Delimiter (Semicolons Instead of Commas) {#delimiter-issues}

Symptom

All data from each row appears in a single cell in column A, with the values separated by semicolons, pipes, or tabs rather than being split across multiple columns.

Why It Happens

"CSV" means Comma-Separated Values, but some software and countries use different delimiters:

• Semicolons: Common in Europe (because commas are used as decimal separators there)
• Tabs: Common in TSV (Tab-Separated Values) exports from databases
• Pipes: Used in some banking and accounting software exports

How to Fix It

Using Text to Columns:

1. Select the column containing all unsplit data
2. Go to Data → Text to Columns
3. Choose Delimited → Next
4. Check the correct delimiter (Semicolon, Tab, Pipe, or Other)
5. Click Finish — Excel splits the column correctly

Using Power Query:

In Power Query (Data → Get Data → From File → From Text/CSV), Excel detects the delimiter automatically and shows a preview. If it guesses wrong, you can manually specify the delimiter in the preview window.

Changing the delimiter globally:

If you regularly receive semicolon-separated files, change Windows' list separator setting:

1. Control Panel → Region → Additional Settings
2. Change "List separator" from semicolon to comma
3. Excel will then treat semicolons as data and commas as delimiters

Note: Changing this setting affects all CSV handling on your system, not just bank statements. Consider using the per-import Text Import Wizard instead.

These formatting issues are all fixable, and most can be prevented by using a consistent conversion pipeline. QuickBankConvert outputs CSVs in a standardized format: ISO dates, UTF-8 encoding, comma delimiters, signed numeric amounts, and no extra rows — eliminating all six of the issues covered in this guide before they start. When working with CSVs from multiple sources, convert them through QuickBankConvert first for a clean, consistent baseline, then merge and analyze with confidence.