Ever double-click a bank statement CSV and watch everything smash into column A? Yep, been there. It tanks reconciliations, messes up dates and amounts, and eats time you don’t have.

Here’s what’s going on: Excel tries to guess how your file is structured—delimiter, encoding, even date order. When that guess is wrong (and bank exports love regional quirks), you get the one-column mess. Let’s fix it fast and make sure it doesn’t come back.

What you’ll learn:

• Why this happens: delimiter mismatches, decimal comma vs point, encoding, text qualifiers, and date formats.
• Quick fixes: Import via Data > From Text/CSV and, if needed, Text to Columns.
• Ongoing workflows: Power Query setups you can reuse every month.
• When to tweak Excel or Windows/Mac separators.
• Bank quirks: negatives in parentheses, trailing minus, currency symbols, multi-row headers.
• How BankXLSX converts bank CSVs to clean, analysis-ready Excel without manual cleanup.

If “Excel not splitting CSV columns” keeps popping up, this guide gives you the simple, repeatable playbook.

Overview: Why bank statement CSVs open in a single column in Excel

When everything lands in column A, Excel guessed wrong. Double-clicking a CSV makes Excel decide the delimiter (comma, semicolon, tab), text qualifier (usually double quotes), file encoding (UTF-8 vs ANSI), and date format without your input.

Banks export files using local rules. Think semicolons instead of commas because commas are used as decimal separators, or dates like DD/MM/YYYY. Excel isn’t great at reading that in one shot, so you end up with one long column and totals that don’t add up.

Common real-world case: European bank CSVs often use semicolons and decimal commas. If your system expects commas, Excel won’t split the columns. Descriptions with commas cause trouble too if the file isn’t quoted properly.

Simple mindset shift: treat CSVs like data to import, not documents to open. Start a blank workbook and use Data > From Text/CSV. You control delimiter, encoding, and locale. Less cleanup, more certainty.

Identify the symptoms and confirm the cause

First, figure out what went wrong. All data jammed in column A? That screams delimiter problem. Weird characters like “é” or “—”? That’s encoding. Numbers that won’t sum or show green triangles? They’re text. Dates sorting like text (01/10/2024 sitting above 01/02/2024)? Locale mismatch.

Quick checks:

• Open the CSV in a text editor. Do you see commas or semicolons between fields? Are values wrapped in double quotes?
• Look for decimal commas (1,23) vs decimal points (1.23). That hints at the locale.
• In Excel, try Data > Text to Columns on column A. Pick Delimited, then test Comma and Semicolon. Check the preview—does it split cleanly?

If the preview only looks right when Text qualifier is set to double quotes, your descriptions include commas and need the quotes respected. That’s where “Excel text qualifier double quotes for CSV” matters. Quick nerd trick: count likely delimiters with LEN(A1)-LEN(SUBSTITUTE(A1,";", "")) to spot the best separator before you import.

Root causes explained: delimiters, separators, encoding, and dates

• Delimiters: Banks use commas, semicolons, or tabs. If Excel expects the wrong one, nothing splits. This is the classic “Excel import CSV delimiter semicolon” problem.
• Regional separators: Decimal commas (1,23) clash with comma-delimited CSVs. Many banks switch to semicolons to avoid that. If Excel expects decimal points, you’ll get numbers as text.
• Encoding: Names with accents or currency symbols need UTF-8. If Excel guesses ANSI, you get “Excel UTF-8 encoding garbled characters.” Choose UTF-8 during import.
• Text qualifiers: Descriptions often include commas. If the file uses quotes but Excel doesn’t detect them, fields split wrong—or not at all.
• Date formats: DD/MM/YYYY vs MM/DD/YYYY vs YYYY-MM-DD. If Excel reads the wrong order, dates flip or become text.

Why finance feels the pain: banks throw in extras like trailing minus for negatives, extra header rows, and running balances. If the CSV doesn’t parse correctly, your reconciliations and checks fall apart. Good news: set types and locale during import (Power Query works great), and most of this disappears.

Quick fix the right way: Import instead of opening the CSV

Don’t double-click. In Excel, go to Data > From Text/CSV. Pick the file, then choose the correct delimiter (Comma or Semicolon), set File Origin/Encoding to UTF-8, and confirm double quotes are picked up as the text qualifier. Load to a table when the preview looks clean.

Try this flow:

• Windows: Data > Get Data > From Text/CSV. Use UTF-8; test Comma, then Semicolon. If available, set Data Type Detection to “Based on entire dataset.”
• Mac: Data > Get Data (or From Text). Use “Mac Excel import CSV UTF-8,” confirm delimiter and text qualifier in the preview.

Bonus move: click Transform Data first. Set each column’s type, and for dates use “Using Locale.” Save the workbook. Next month, replace the source file and hit Refresh. If you’d rather skip manual work for tricky banks, convert bank CSV to Excel xlsx with BankXLSX and start from a clean sheet.

One-off rescue: Use Text to Columns after it opened in one column

Already opened it and everything’s stacked in A? Select column A > Data > Text to Columns. Choose Delimited > Next. Check the right delimiter (Comma or Semicolon). Set Text qualifier to double quotes so full descriptions stay intact.

On the last step, assign data types: Date (DMY or MDY), Text for IDs and account numbers (to keep leading zeros), Number for amounts. That’s the fast “how to use Text to Columns in Excel for CSV” fix.

Example: you get a semicolon CSV with decimal commas like 1,23. Pick Semicolon as delimiter, set Amount to Number, and you’ve got real decimals. Trailing minus for negatives? Quick Find/Replace, or handle it later in Power Query.

Small but helpful tip: format sensitive columns (account numbers) as Text first to avoid scientific notation. If you do this often, record a tiny macro to run Text to Columns with your go-to settings, including the “Excel text qualifier double quotes for CSV” piece.

Make it durable: Build a reusable Power Query import

Do this once and reuse it every month. Data > Get Data > From Text/CSV > Transform Data. Set delimiter and File origin = UTF-8, and verify quotes are recognized.

• Remove extra header rows, then promote headers.
• Set types: Date (Using Locale), Decimal Number for amounts, Text for IDs.
• Clean text: Trim, Clean, and strip non-breaking spaces.
• Fix negatives: trailing minus and parentheses to normal negative values.
• Split Amount into Debit/Credit with conditional columns if you need both.

This “Power Query import bank statements” setup keeps things consistent and easy to audit. Consider parameters for file path or use From Folder to drop new CSVs in and refresh. Many teams convert bank CSV to Excel xlsx after the query so they pass around a stable .xlsx instead of raw CSVs. Extra perk: locking data types here protects you when the bank tweaks export formats mid-year.

Align Excel and system regional settings (when you must)

You can force separators globally, but use caution. In Excel: File > Options > Advanced, uncheck “Use system separators,” then set Decimal and Thousands separators to match the file (e.g., decimal comma). On Windows, the “Excel list separator Windows settings” live under Control Panel > Region > Additional settings; changing List separator to semicolon can help with semicolon CSVs.

Example: a DACH team uses decimal commas. If you flip Excel for US files, local work might break. Better approach: keep system defaults, and use the import wizard’s file-specific settings instead.

Middle-ground that works well: keep a small library of workbook templates—one per bank—each with the right delimiter, encoding, and locale saved in the connection. If you must change system separators, document it and add a checklist step to switch back after close.

Ensure correct dates, numbers, and currency formats

Dates: Match the file’s order. UK banks often use DMY, US use MDY, and many exports use ISO (YMD). “bank statement CSV date format DMY vs MDY” mix-ups are a top cause of sorting and filter weirdness. In Power Query, set Date > Using Locale and pick the right one, no matter your PC’s settings.

Numbers: Won’t sum? They’re text. The classic “numbers imported as text in Excel fix”: strip currency symbols, normalize decimal separators, then set type to Decimal Number in Power Query. Parentheses or trailing minus for negatives? Replace patterns or create a quick custom column to multiply by -1 where needed.

Currency: Keep a Currency column. If the statement doesn’t include one, add it in the query as a constant per source. For multi-currency, store amounts as numbers and apply currency format later in reports—not during import.

Add a small validation page: total rows, total of Amount, and “Opening + Net = Closing.” Color-code the results (OK/Warning/Error) so issues show up before anyone posts.

Platform differences: Windows vs Mac vs older Excel

Windows and Mac both support From Text/CSV and Power Query, but the menus look a bit different. Windows puts Get Data front and center with a clear preview. Mac has similar options; labels vary depending on your version. Older Excel versions may rely on the legacy Text Import Wizard, and Power Query might be limited.

Practical pointers:

• Windows 365/2021: Use From Text/CSV, then Transform Data to lock types and locale.
• Mac 365: Use “Mac Excel import CSV UTF-8,” confirm delimiter and quotes in preview, check encoding.
• Older versions: Use the legacy Text Import Wizard (Data > Get External Data > From Text) or Text to Columns.

If your Mac preview looks fine but names still break, try importing through Power Query instead of the legacy wizard—its engine handles encoding and quotes more consistently. For mixed environments, ship a template workbook that behaves the same across both.

Bank-specific quirks and how to normalize them

Banks love to be unique. Expect a few oddities:

• Intro rows or multi-line headers: Remove top rows, then promote headers.
• Descriptions with commas or line breaks: Make sure double-quote qualifiers are honored.
• Single Amount vs Debit/Credit: Split with a simple conditional rule.
• Running balance fields: Great for checks, sometimes inconsistent.

Example flow: a file with semicolons, decimal commas, two header rows, and negatives in parentheses. In Power Query, set delimiter to Semicolon, File Origin to UTF-8, remove top 2 rows, promote headers, tag parentheses as negatives, then convert Amount to Decimal Number. Need separate columns? Add Debit and Credit columns with a condition.

Extra value: map merchant categories or reference codes to your chart of accounts inside the query. Then convert bank CSV to Excel xlsx in your final layout. You’ll skip a ton of manual reordering at the end.

Quietly powerful addition: map merchant category codes, statement references, or transaction types to your chart of accounts inside the query. This converts a raw feed into pre-categorized lines ready for posting. Combine with a standard output to convert bank CSV to Excel xlsx with columns aligned to your accounting import—no more last-mile rearranging.

Step-by-step “fix it now” checklist

Short on time? Do this:

• Open Excel, start a blank file.
• Data > From Text/CSV.
• Preview: set File Origin = UTF-8, try Comma, and if not right, Semicolon.
• Text qualifier should be double quotes. Make sure dates and amounts look good.
• Transform Data: set types (Date, Decimal, Text). Use Using Locale for dates.
• Load to a table and save.

If you already opened it and everything’s in one column:

• Select column A > Data > Text to Columns > Delimited.
• Try Comma, then Semicolon. Set Text qualifier to double quotes.
• Assign data types (DMY/MDY, Text for IDs, Number for amounts).
• Save as .xlsx.

Quick checks:

• Column count matches the expected layout.
• Amount total matches the statement.
• Dates sort correctly and numbers actually sum.

If this is a weekly thing, build a one-time Power Query and make next time a simple Refresh.

Prevent repeat issues: SOPs and templates for your team

Stop fixing the same problem over and over. Make a simple kit:

• One “Bank Import Template.xlsx” per bank with a saved query. It locks delimiter, encoding, quotes, and locale. New month? Swap the file and Refresh.
• Keep templates in a shared folder. Add a readme with the bank’s export details (delimiter, date format, decimal separator).
• Use From Folder in Power Query to combine monthly files that match. Add a Period column from the filename.
• Build a checks sheet that flags column count changes, mismatched totals, or balance issues.

Some teams document “Excel list separator Windows settings,” but prefer file-specific imports to avoid system changes. If you have multiple regions, keep a central index of templates and a master consolidation workbook. For PDFs or weird text files, convert to a tidy CSV or xlsx first, then feed your template. Your downstream models will thank you.

Automate conversion and normalization with BankXLSX

Multiple banks, different locales, various file types (CSV, TXT, sometimes PDF exports)? Let a tool handle the messy parts. BankXLSX converts bank CSV to Excel xlsx with consistent columns—Date, Description, Amount or Debit/Credit, Balance, Currency, Reference—and figures out delimiter, UTF-8, decimal commas vs points, and quotes for you.

How it fits your day-to-day:

• Upload the statement you downloaded.
• BankXLSX detects the format, keeps quoted descriptions intact, and fixes dates and numbers.
• Preview, adjust mapping if needed, and download a clean .xlsx ready for recon or import.
• Save a profile per bank so next month takes seconds.

Nice side effect: by standardizing early, your Excel models and Power Query imports stay stable. You can export separate Debit/Credit, handle trailing minus and parentheses, and force Text where IDs need to keep leading zeros. It makes month-end far less stressful.

Security and compliance when handling statements

Bank data is sensitive. Treat it with care.

• Access: store files with least-privilege access and unique logins.
• Transfer: use SFTP or encrypted links; avoid raw attachments and desktop sprawl.
• At rest: encrypt storage and keep files in a controlled location.
• Retention: align file retention with policy—keep what policy requires, delete the rest. Decide when the converted .xlsx becomes the record.
• Audit trail: track who imported, transformed, and approved. If you use BankXLSX or a workflow tool, make sure logs exist.
• Testing: use masked samples in non-prod environments.

Try a “period close pack” structure: Raw, Processed, Final, Evidence. Add a checklist (source verified, sums validated, balances reconciled). Auditors get a clean path from original CSV to the final numbers without exposing extras.

Troubleshooting matrix: symptoms, causes, fixes

• All data in one column: Wrong delimiter or quotes not detected. Fix: import with the correct delimiter and set Text qualifier to double quotes.
• Garbled characters: Encoding mismatch. Fix: pick UTF-8 during import to solve most “Excel UTF-8 encoding garbled characters.”
• Numbers won’t sum: They’re text or include symbols. Fix: remove currency symbols, convert with the right decimal separator, set type to Decimal Number—classic “numbers imported as text in Excel fix.”
• Dates won’t sort: Wrong locale or text dates. Fix: re-import with the right order or use Using Locale in Power Query.
• Descriptions split: Quotes not handled or delimiter inside text. Fix: ensure Text qualifier is double quotes.
• Wrong sign on negatives: Trailing minus or parentheses. Fix: replace patterns and multiply by -1 where needed.
• Extra rows/columns: Banks added disclaimers or shuffled columns. Fix: remove top rows, promote headers, and map by name in Power Query.

Low-tech safety net: after import, add quick checks—row count, column count, and a SUM of Amount. Use conditional formatting to flag blanks in key fields. Fast and effective.

FAQs

Why does Excel ignore my delimiter when I open a CSV? Double-clicking makes Excel guess. If the bank uses semicolons but your system expects commas, it won’t split. Use the import wizard and set “Excel import CSV delimiter semicolon.”

Can I force Excel to always use UTF-8 and a specific delimiter? Not for double-click opens. Use Data > From Text/CSV and save a query connection. For repeat files, build a Power Query or standardize them first.

How do I keep account numbers from reformatting? Import those columns as Text. Avoid opening CSVs directly or Excel might turn long IDs into scientific notation.

What if my bank only provides PDF statements? Convert PDFs to structured data first, then import. A conversion service gives you consistent columns and fewer surprises.

How do I fix dates that imported wrong? Re-import with the correct locale (DMY/MDY) or, in Power Query, use Date > Using Locale.

Can I split Amount into Debit and Credit reliably? Yes. In Power Query: Debit = if [Amount] < 0 then -[Amount] else null; Credit = if [Amount] > 0 then [Amount] else null. Or let your converter output both.

Key Points

• Excel guesses delimiter, encoding, and locale when you double-click. If it guesses wrong, everything lands in column A.
• Use Data > From Text/CSV and set delimiter, text qualifier ("), UTF-8, and date order. If it’s already open in one column, run Text to Columns.
• For repeat work, build a Power Query that sets types, fixes negatives, removes symbols, and applies locale-aware dates—no need to touch system separators.
• Best practice: keep a template with validations (sums, date order, opening+net=closing), or use BankXLSX to detect formats and output clean, analysis-ready .xlsx fast.

Conclusion and next steps

Files open in one column because Excel guessed wrong on delimiter, encoding, or date order. The fix is straightforward: import with Data > From Text/CSV, choose the right delimiter and quotes, pick UTF-8, and set the date locale. For a quick rescue, use Text to Columns. For month-end, save a Power Query and refresh each period.

If you want to skip the fiddly parts, BankXLSX converts bank statements into clean, ready-to-use Excel. Try it now—grab a free trial or book a short demo and get your time back.