Bank references in Excel: protect long IDs and leading zeros

Treat a transaction reference as an identifier, not a quantity. Import it as text before Excel converts it. Numeric precision is limited to 15 significant digits, and changing a damaged cell to Text afterwards cannot restore the original reference.

Import identifiers as text before numeric conversion

1. Keep the raw CSV or source PDF unchanged. Open an import preview instead of double-clicking the CSV, and assign Text to reference and account-identifier columns.

2. Inspect any automatically added type-conversion step. Remove or replace numeric conversion for identifiers before loading the table.

3. Compare complete strings with the raw source, including initial zeros and the final digits. Save the working copy as XLSX when retaining column types matters.

Fictional example

The fictional reference 001234567890123456 must remain that exact 18-character string. Displaying 1.23457E+15 is a warning to inspect the underlying value, not proof that the full ID survived. Re-import the original as text and compare the last four characters 3456. Do not reconstruct missing digits by padding the altered number.

Set the type before the first lossy step

Import identifiers as text before any automatic numeric conversion, preserving every digit and leading zero. In Excel, start with the Data tab’s Text/CSV import and open the transformation editor when available. Inspect the steps in order. If a numeric conversion already appears, replace that conversion for the reference column rather than adding a Text step after it. The important checkpoint is the value immediately before loading: it must still read 001234567890123456 in full.

Keep amount columns numeric where appropriate; making the reference textual does not mean converting the whole transaction table to text. If the import options differ in your Excel version, use its documented text-import method and check the same outcome. The procedure succeeds because the identifier never becomes a number, not because a particular display format looks correct.

Distinguish display problems from lost identity

A narrow column or scientific display first calls for inspecting the stored value against the untouched source. If the complete string survives, correct the presentation. If zeros or final digits have already changed, start a fresh import from the raw file. If the raw export is already damaged, return to the original bank document or obtain a new export; an XLSX copy of the damaged data cannot recover it.

Use two fictional checks: 00123 must stay five characters, while 001234567890123456 must stay eighteen. Compare every character, not only the suffix 3456: a missing leading zero would otherwise pass that suffix check. Save, reopen the working copy and compare again. When handing over a CSV, include the reference-as-text instruction so the next recipient does not repeat the same conversion.

Detect references that collapse to the same value

Test more than one long identifier. A damaged column can still look unique in a small sample, while two real references differing only in their final digit become indistinguishable. These four values are invented and belong in a separate import fixture, not in a live transaction file.

Fictional identifier	Required stored type and length	Specific check
00123	Text; 5 characters	Both leading zeros survive
001234567890123456	Text; 18 characters	Full string, including suffix 3456, matches
1234567890123451	Text; 16 characters	Final 1 survives
1234567890123452	Text; 16 characters	Final 2 survives and remains distinct from previous row

In a numeric conversion limited to 15 significant digits, the last two references can both become 1234567890123450. Converting those results to text gives two copies of the same damaged identifier. A full-string comparison against the raw source catches the loss; a check of row count or amount totals does not. In this fixture there must be four exact, distinct stored strings after import, save and reopening.

Imagine the final two references identify fictional debits of EUR 7.20 each. The expected total is −14.40. Both rows may still sum to −14.40 after their references collide. Do not remove a row as a duplicate based on the altered ID: that would change the total to −7.20 and discard a transaction without source evidence. Resolve the reference import first, then assess any genuine duplication with the source rows.

For handover, keep a small field contract: reference is text, no padding, no automatic numeric conversion and no trimming unless the source specification permits it. A missing reference stays missing; neither 0 nor an invented run of zeros restores it. Inspect the file received by the next person, because correct types in your workbook do not travel with a CSV. If your Excel edition offers automatic-conversion settings, use them only as an additional control; the same exact-string fixture must still pass.

Checks and limits

Test an identifier longer than 15 digits and one beginning with zero. A custom number format may change appearance without repairing stored data. Reopening a CSV can trigger conversion again because CSV does not preserve spreadsheet cell types. Verify the actual saved and reopened file, not only the first import preview.

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

Related checks

Preserve the reference when rebuilding descriptions and importing CSV fields. Bank statement descriptions split across rows: join them safely · 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

• W3C — Model for Tabular Data and Metadata on the Web.
• RFC 4180 — CSV.