You converted a bank statement PDF to Excel and the numbers look off. Commas and periods swapped places, totals won’t add up, and those little green triangles are everywhere. Annoying, but fixable.

This usually isn’t Excel being weird. It’s a locale thing: some regions use 1,234.56, others use 1.234,56. Toss in CSV delimiter confusion, OCR hiccups from PDFs, and the “Use system separators” setting, and Excel misreads the amounts.

Below, I’ll walk you through quick checks, safe fixes (no risky global replaces), and a few “gotchas” like parentheses negatives and thin spaces. I’ll also show a repeatable approach and how to avoid the mess next time with BankXLSX.

Overview — what this problem looks like after conversion

Freshly converted file, everything looks normal at first, then… chaos. Amounts that should be 1,234.56 show up as 1.234,56 (or the other way around). Values sit left‑aligned, SUM gives you zero, sorting puts 10,000 before 2,500, and you might see odd characters like 1 234,56 (thin space), 1’234.56 (apostrophe), or a trailing minus sign like 123,45−.

This isn’t cosmetic. If you post these to your GL or build a pivot, you can end up with totals that don’t match the PDF. That’s how month‑end gets messy fast.

A common case: a European PDF becomes Excel text with commas for decimals, but your Excel expects US separators. Boom: numbers stored as text that won’t sum. Quick tip before you do anything else—run a quick SUM on the Amount column and compare it to the statement total. If it doesn’t match, treat the whole column as suspect until you normalize it.

Root cause — locale, separators, and import behavior

Different regions write numbers differently. US/UK use 1,234.56. Many European countries use 1.234,56. Switzerland might throw in 1’234.56 with an apostrophe. On top of that, some statements use non‑breaking spaces (U+00A0) or thin spaces (U+202F) for thousands. Excel doesn’t always guess that correctly.

CSV files add another twist. In places where a comma is the decimal mark, CSVs often use semicolons as the delimiter. If you import those as comma‑delimited, commas inside numbers get split across columns.

OCR from PDFs can also swap punctuation or insert invisible characters. And Excel, by default, follows your system’s decimal and thousands separators. If that doesn’t match the file, numbers turn into text or wrong magnitudes. Sometimes import tools strip thousands separators in some rows but not others, which quietly messes up cents. Not fun to unwind.

Quick diagnostics — confirm what went wrong before fixing

• Visual check: Are amounts left‑aligned with green triangles? Do you see 1’234 or 1 234?
• Count marks: use LEN(A2)-LEN(SUBSTITUTE(A2,",","")) to count commas, and similar for periods and spaces. Helps you spot the pattern fast.
• Open the CSV in a text editor: see commas or semicolons between columns? That’s your delimiter clue.
• Match a few rows against the PDF. If 1.234,56 consistently became 1,234.56, you’ve got a simple decimal swap. If it’s random, OCR or stray Unicode spaces might be the culprit.

One more quick test: SUM the column. If you get zero, Excel sees text. If the total is massive, thousands separators were likely removed. Build a tiny “intake” sheet with these tests so every file gets the same two‑minute triage.

Fix method 1 — set Excel separators and coerce text to numbers

Got a one‑off file? Make Excel speak the file’s language, then convert.

• Windows: File > Options > Advanced > Editing options. Uncheck “Use system separators.” Set the Decimal and Thousands characters to match the file.
• Mac: Excel > Settings > Advanced > Editing options does the same thing.

Now convert the text into numbers:

• Click the warning icon and choose Convert to Number, or
• Data > Text to Columns > Finish, or
• Use =VALUE(A2) in a helper column.

Example: If the CSV uses commas for decimals, set Excel’s decimal to comma and thousands to period. Convert to numbers, then switch your separators back if you want US‑style display. The values stay correct.

Quick sanity check: look at decimal precision. If some rows show whole dollars where you expect cents, OCR might have dropped punctuation. Flag them with a custom format or a formula so they don’t slip through.

Fix method 2 — safe Find/Replace with a placeholder

Don’t nuke punctuation with a blind replace. Use a placeholder to protect decimals. European to US (1.234,56 → 1,234.56):

1. Replace comma with ^ (or another character not in your data).
2. Replace period with nothing (removes thousands).
3. Replace ^ with a period.
4. Coerce to numbers with Text to Columns or VALUE.

Reverse it for US to EU. This avoids turning 1.234,56 into 123456 by accident. Seen it. Hurts.

• First remove non‑breaking and thin spaces (copy the exact space from a cell into Find and replace with nothing).
• Test on a copy of the sheet. Verify three subtotals against the PDF before moving on.

Pro tip for teams: agree on a standard placeholder (like ^) and write the three steps into your SOP. Everyone does it the same way, and reviews get faster.

Fix method 3 — Text to Columns with Advanced locale options

Tell Excel exactly what your decimal and thousands separators are, and let it parse correctly.

• Select the amounts column.
• Data > Text to Columns > Delimited > Next > Next.
• Click Advanced.
• Set Decimal and Thousands separators to match the text (for example, comma for decimal, period for thousands).
• Finish.

This cleanly turns 1.234,56 into a real number without placeholder tricks. It’s great when the data is consistent but your Excel settings aren’t.

Real‑world tip: jot a short note in the sheet (or a hidden tab) with the separators you used. If someone needs to re‑create your steps later, they won’t have to guess.

Fix method 4 — Power Query “Change Type using Locale” (repeatable and safest at scale)

Power Query is your friend when you do this every month.

• Data > Get Data > From Table/Range (or From Text/CSV).
• Make sure Amount is text in the preview.
• Right‑click Amount > Change Type > Using Locale.
• Choose Decimal Number and the matching locale (for example, German for 1.234,56).
• Load to Excel.

Set “Data Type Detection” to “Do Not Detect Data Types” when importing, so Excel doesn’t guess wrong. Then you decide the types using locale. Next time, refresh and you’re done.

Example: US, German, and Swiss files in one workbook. Replace apostrophes in Swiss numbers, convert all using locale, and you’ve got an audit‑friendly list of steps Power Query records for you.

Fix method 5 — formula-based normalization

Prefer formulas so everything is visible on the sheet? Try these.

EU → US:

=VALUE(SUBSTITUTE(SUBSTITUTE(A2,".",""),",","."))

US → EU (as text if your Excel expects comma decimals):

=SUBSTITUTE(SUBSTITUTE(TEXT(VALUE(A2),"0.00"),".","#"),",",".")

Then replace # with , and coerce as needed.

Strip odd separators first (apostrophes, thin spaces, non‑breaking spaces), then convert:

=LET(x,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160),""),UNICHAR(8239),""),"'",""), VALUE(SUBSTITUTE(SUBSTITUTE(x,".",""),",",".")))

If you’re on Microsoft 365, wrap these into Lambda functions like TO_US_DECIMAL() and TO_EU_DECIMAL() so your team can reuse them and audit the logic easily.

Importing CSV/TXT without corrupting decimals

Start by checking the delimiter. Open the file in a text editor. If you see semicolons, import with semicolon or the commas inside numbers will explode across columns.

In Excel: Data > Get Data > From Text/CSV. In the preview, pick the correct delimiter and set Data Type Detection to “Do Not Detect Data Types.” Load to Power Query, then use Change Type Using Locale on the amount columns.

• This avoids the classic comma‑as‑decimal getting split during import.
• Build a “landing” query that always removes non‑breaking spaces, fixes minus signs, and applies the right locale. Treat it like a small staging area before your analysis.

It takes a minute to set up, but it saves you from re‑doing the same cleanup steps by hand every month.

Edge cases you’ll see in bank statements (and how to handle them)

Banks get creative. You’ll see things like:

• Parentheses negatives: (1,234.56)
• Trailing minus: 123,45−
• CR/DR notation: 123,45 CR or DR 123,45
• Apostrophes: 1’234.56
• Thin or non‑breaking spaces: 1 234,56 or 1 234,56
• En dashes instead of minus signs: – vs -

Handle them in this order:

• Turn parentheses into negatives before parsing.
• Move trailing minus signs to the front.
• Map CR/DR to a sign based on the bank’s convention.
• Strip apostrophes and weird spaces.

In Power Query, replace Unicode hyphens with a standard minus, remove UNICHAR(8239) and CHAR(160), handle parentheses, then change type using locale. Watch for format changes at page breaks—files sometimes switch styles mid‑document.

System-level settings that influence Excel parsing

Three places can affect how Excel reads numbers:

• Excel: “Use system separators” and the custom decimal/thousands characters.
• Windows Region: decimal symbol, digit grouping, list separator.
• macOS Language & Region: decimal and grouping separators.

Changing system settings affects other apps too, so use with care. For most tasks, tweak Excel’s separators temporarily and change them back when you’re done.

Two handy habits:

• Note the active separators in the workbook (tiny note on a hidden tab). If someone reopens it later on a different machine, they know what you used.
• If you often switch locales, consider a separate user profile or VM with alternate region settings. Saves you from surprise behavior when multiple people touch the same computer.

When you need US display but ingest EU decimals, keep Excel display settings as US and use Power Query’s Using Locale to convert the values properly.

Prevent issues at the source with BankXLSX

Best fix? Export numbers that are already right. BankXLSX converts bank statement PDFs to Excel/CSV and respects your chosen numeric style.

• Pick US decimal point or European decimal comma.
• Normalize commas, periods, thin spaces, non‑breaking spaces, and apostrophes.
• Detect negatives written with parentheses or trailing minus, and interpret CR/DR correctly.
• Preview and validate totals before you download.

Example: a group operating in six countries set up per‑bank templates in BankXLSX. Upload, apply the correct locale and sign rules, export, done. Their month‑end review got faster and the numbers matched the statements the first time.

Treat these templates like internal controls—version them, review them, and stop reinventing the steps every month.

Validation checklist before posting to GL/BI tools

• Math check: Opening + SUM(Transactions) = Closing.
• Row count: match the PDF and look for duplicates.
• Spot‑check a handful of entries against the statement.
• Outliers: highlight amounts way outside the norm.
• Data types: right‑aligned, no green triangles, ISNUMBER is TRUE.
• Currency: symbols or ISO codes match what you expect.

If the total is zero, you’ve got text. If it’s way too big, thousands separators were probably stripped. If cents look inconsistent, re‑parse with the right locale rules.

One more control: keep both the raw text column and the parsed numeric column for a minute. Totals should match to the cent. Add a tiny log with who checked it and when. Auditors love that.

Common workflows and step-by-step recipes

Recipe A: EU (1.234,56) → US (1,234.56)

• Replace , with ^; replace . with nothing; replace ^ with .; then coerce to numbers.
• Check SUM against the PDF total.

Recipe B: US (1,234.56) → EU (1.234,56)

• Replace . with ^; replace , with .; replace ^ with ,; then coerce to numbers and set display to comma decimals.

Recipe C: Power Query using locale

• Import with “Do Not Detect Data Types.” Change Type Using Locale to match the source punctuation. Load to Excel.

Recipe D: CSV intake

• Check the delimiter in a text editor. Use the right one, then parse with locale.

Package these as quick SOPs with a couple screenshots. New teammates can follow them without guessing, and reviews become checklists instead of detective work.

FAQs

Why does Excel flip commas and periods?
Because Excel follows either your system separators or app settings. If the file uses a different convention, Excel treats the values as text or reads the number wrong.

Why won’t my totals sum?
They’re text. Use Text to Columns, VALUE(), or Power Query Using Locale to convert them to real numbers.

Can formatting fix this?
No. Formatting changes how a number looks, not how text turns into a number. You need a proper parse.

How do I handle CSVs from Europe?
Many use semicolons as the delimiter. Import with semicolon so commas can stay inside numbers. Then parse using locale.

What about OCR errors from PDFs?
Clean up punctuation and odd spaces first, then parse. Otherwise you lock in bad characters.

How to handle CR/DR and trailing minus?
Apply the sign rule first (CR/DR or trailing minus), then convert the text to a number.

Do I need to change system settings?
Usually no. For most cases, use per‑file tools like Text to Columns or Power Query’s Using Locale. Change system settings only if you control the environment.

When to automate — scaling beyond one-off fixes

If you’re juggling multiple banks, currencies, and countries, the one‑off tricks get old fast. Clues you’ve outgrown manual fixes: recurring last‑minute surprises, a pile of fragile spreadsheets, and reviews that spend more time on mechanics than numbers.

Standardize the process: keep the raw file, the transformation steps, and the validated output with a control total. If you stick with Excel, build Power Query templates and disable auto type detection so you control the parse.

If you want the easy route from PDF to clean numbers, use a conversion flow that bakes in locale rules and shows a preview before you export.

Key Points

• It’s a locale mismatch: decimal comma vs decimal point, plus delimiter mix‑ups and OCR quirks, push Excel to treat amounts as text or the wrong values.
• Diagnose quickly: look for left alignment and green triangles, count commas/periods with simple formulas, confirm the CSV delimiter, and compare a quick SUM to the statement.
• Fix it right: set Excel’s separators and convert, do a safe placeholder swap, use Text to Columns (Advanced), or go with Power Query Using Locale for repeat work.
• Better yet, avoid it: convert with BankXLSX to normalize separators, handle negatives and CR/DR, preview totals, and export clean XLSX/CSV. Always validate before posting to your GL or BI tools.

Conclusion and next steps

Swapped commas and periods aren’t random. They’re almost always a locale/import issue. You can fix them fast with Excel’s separators, a careful placeholder swap, Text to Columns (Advanced), or Power Query Using Locale. Clean up edge cases like parentheses, trailing minus, and odd spaces, then validate against the PDF.

Want to skip the cleanup? Use BankXLSX. Upload your statement, pick your number style, preview the result, and download an XLSX/CSV that adds up the moment it opens. If you’re processing statements month after month, try a template or grab a quick demo and see it in action.