Bank amounts stored as text: find invisible thousands spaces

If an amount looks numeric but is excluded from totals, inspect its actual characters before changing separators. An ordinary space, a nonbreaking space and a narrow nonbreaking space can look almost identical. Clean only the amount field, keeping the raw text for comparison.

Identify source separators before numeric conversion

1. Copy a failing value into a review column. Identify the decimal mark and grouping mark from several source amounts, including one below 1,000.

2. Replace only the confirmed grouping character in the numeric column. Then convert with the source locale explicitly; do not replace punctuation across the whole sheet.

3. Count conversion errors and compare every changed value with its raw counterpart. Keep unresolved values as errors rather than turning them into zero.

Fictional example

A fictional amount contains 1 234,50: the gap is U+202F, not a normal space. After removing that verified grouping mark and interpreting comma decimals, it becomes numeric 1234.50. A second value 12,34 must remain 12.34. Removing every comma would instead turn it into 1234, creating a hundredfold error.

Isolate the character instead of changing the display

Start with an untouched text column, a cleaned-text column and a numeric result. Copy the actual gap from the failing amount into the replacement field; typing a normal space may target a different character. Restrict the replacement to the amount column. Record that the observed character is being removed as a thousands separator, not because every invisible character is disposable.

Inspect the exact code point instead of assuming that a generic space-cleaning step removes every separator. A currency format also does not explain why the underlying value failed to convert. Inspect the raw string again if the problem persists; do not keep adding replacements until the cell happens to sum.

Test the rule before applying it to the column

Use the fictional 1 234,50 and 12,34 as two control rows. Set comma as the confirmed decimal separator for this source, regardless of the language of your computer. Set the confirmed decimal and grouping conventions explicitly before converting the amount column. The expected numeric sum is 1246.84, with two successful conversions and no change to row count.

Add a third review case, 1 23,50. In this fictional source, thousands groups require three digits, so that malformed group must be checked against the PDF; removing its space would manufacture an apparently plausible 123.50. Keep its result unresolved. Review each amount independently before accepting a batch total. If the bank’s native numeric export already avoids the character problem, prefer importing that file with its documented settings.

Distinguish look-alike spaces and reject silent defaults

In this separate fictional test set, the document uses comma decimals and three-digit thousands groups. Each of the first three rows is EUR 1,234.50, but their source strings contain different characters.

Raw text	Character between 1 and 234	Treatment after source confirmation	Expected number (EUR)
1 234,50	U+0020 ordinary space	Remove this grouping character	1234.50
1 234,50	U+00A0 nonbreaking space	Target U+00A0 specifically	1234.50
1 234,50	U+202F narrow nonbreaking space	Target U+202F specifically	1234.50
1 23,50	Group contains only two digits	Hold for source review	Unresolved
Empty field	No number to interpret	Check missing/inactive-field meaning	No invented zero

Unicode identifies U+202F as a distinct space. Identify the character with a Unicode inspection facility in your editor, or copy the exact gap into a limited replacement. If several kinds occur in one column, record and validate each rule; do not assume the first successful row defines the whole file.

Classify empty fields before numeric conversion so an unresolved amount cannot silently become zero. Check missing values before conversion and keep a separate status column. Successful conversion is not validation of grouping syntax. In a single-amount transaction column, an empty field remains unresolved; in separate debit/credit columns, determine whether it is the inactive side before treating it as missing.

For this test set, require three confirmed numeric results totalling EUR 3,703.50 and two explicitly classified exceptions. Count actual numeric values rather than relying on right alignment or currency formatting. If an unexpected fourth number appears, inspect the malformed or empty row before accepting the total.

Checks and limits

Check total, minimum, maximum and the number of numeric cells. A single grand total can hide compensating errors. Do not strip spaces from descriptions or references as part of this operation. Unicode documents locale-dependent number symbols; the bank source and explicit import settings still determine your conversion rule.

Keep the original file unchanged and record any unresolved item before using the table.

Related checks

Treat numeric conversion separately from the debit and credit convention. Debit and credit columns to one signed amount: a bank CSV method · Check a bank CSV before opening it in Excel.

BankStatementScan is in preparation. The site offers an access request and a fictional example; it does not currently convert your bank files. See the planned service.

Sources consulted on 16 October 2026

• Unicode — Number formatting.
• Unicode — General Punctuation.