Excel changed my bank reference numbers: what can I recover?

Check the original file before changing any cells

The workbook isn't the place to start, because the CSV may still contain the full reference. Open a copy in a plain-text editor, rather than double-clicking it into Excel, and compare one affected row with the workbook. If the file still says 001234, those zeros are available for a fresh import. If it already says 1234, Excel can't tell you how many zeros preceded it.

Microsoft's CSV import guide, updated June 25, 2026, says opening a CSV uses the current default data-format settings to interpret its columns. That's why the file on disk and the cells you see can disagree. You need an untouched download, an earlier intact copy, or a new export from the bank to work from.

Account numbers, check numbers and payment references identify things. You don't need arithmetic on them, so keep them as text even when every character happens to be a digit. Amounts and dates need their own conversion choices; the bank CSV date and number guide covers those separately.

Scientific notation alone doesn't mean digits were lost

A fictional value of 123456789012 can display as 1.23E+11 in a two-decimal scientific format while the underlying value remains intact. To find out whether that's happened, select the cell and compare what's in the formula bar with the source. Number format with zero decimal places and a wider column can expose the stored digits if it's only a display problem. Neither can supply digits that are no longer stored.

Microsoft's scientific-notation guidance, updated August 13, 2026, distinguishes the number format from the cell value and points to the formula bar. It also warns that numbers longer than 15 digits can have an altered underlying value. The formula bar shows what Excel has now, not necessarily what the bank sent.

Four fictional references show three different failures

This small fictional fixture gives you a way to check your import route without using real account or card numbers. Save it as a separate text or CSV file. You're aiming for four rows with the Reference column preserved character for character.

Row,Reference
R1,001234
R2,1234567890123456
R3,1234567890123457
R4,123E5

The middle column below models Excel's documented automatic numeric conversion with the relevant conversions enabled. It isn't a screenshot, and it doesn't mean every import route behaves identically. Microsoft's leading-zero and large-number guidance, updated June 25, 2026, states that Excel has a maximum numeric precision of 15 significant digits. For the positive 16-digit integers below, the digit after position 15 becomes zero. This is a numeric-conversion limit, not a 15-character limit on text identifiers. Leading zeros are a separate issue: even the six-character reference 001234 changes when treated as a number.

Both long references start with the same 15 digits: 123456789012345. The original integers differ by 1, but the converted values differ by 0. The converted value is 6 below the first original and 7 below the second. The final digit is replaced by zero, not rounded to the nearest ten.

The last row is different: 123E5 has become 123 times ten to the fifth power, or 12300000. Microsoft's automatic-conversion documentation, updated June 25, 2026, uses this exact string to illustrate exponent conversion. Once that has happened, turning the number into text can't tell you whether the original identifier contained an E.

Import the original as Text in Power Query

A blank workbook lets you choose how to import the CSV instead of opening it directly. For desktop Excel on Windows, the Microsoft import documentation covers Microsoft 365, Excel 2024 and Excel 2021, as well as older listed editions. Its identifier guide calls the editor button Edit; the Text/CSV connector guide, updated October 8, 2026, calls it Transform Data.

1. Choose Data > From Text/CSV and select the untouched file. The preview should give the reference its own column, so confirm the delimiter there. If everything is in one column, the delimiter problem needs fixing first.
2. If the preview offers data type detection, turn it off so columns default to Text, as the connector guide documents. Open the query editor rather than loading immediately so you can check that each identifier column is Text. For any that aren't, select the column and choose Data Type > Text. Working amount and date columns need separate conversions once you know their conventions.
3. If Excel asks how to change the column type, choose Replace Current, as Microsoft's leading-zero guide instructs. You want to replace the inferred numeric conversion, because adding a Text conversion after it won't undo the damage.
4. The query steps and preview should show whether an earlier step already converts the identifier to a number. If so, remove or correct that conversion before proceeding, then compare the leading zeros, final digits and any literal E with the raw file.
5. Once the text matches, choose Close & Load. Keep the review workbook separate so you don't overwrite the source CSV. A refresh or later export needs another check of the column.

Converting 1234567890123450 to the text string 1234567890123450 leaves you with the same missing digit. If the original CSV is intact, restarting from it is the way back; repairing the last query step by eye isn't.

The Text Import Wizard also lets you choose Text first

The legacy wizard also gives you control before the data reaches the worksheet. The setting you need is on its final page. Microsoft's Text Import Wizard guide, updated August 12, 2026, documents the following Windows route for Microsoft 365, Excel 2024 and Excel 2021.

1. If needed, enable From Text (Legacy) under File > Options > Data > Show legacy data import wizards. Then choose Data > Get Data > Legacy Wizards > From Text (Legacy).
2. With the original file selected, choose Delimited for a separated CSV. The preview lets you check the columns as you select its delimiter and text qualifier.
3. In step 3, select each reference, account or check-number column in the preview and set Column data format to Text, not General, to keep Excel from treating the identifiers as numbers. Finish the import into a new worksheet and compare with the source.

Microsoft also documents changing a copy's extension from .csv to .txt to invoke the wizard when opening it. Renaming a file does not repair its contents; it only changes the route Excel uses to import them.

Newer Excel versions can stop these automatic conversions

Excel for Microsoft 365 and Excel 2024 on Windows and Mac have the controls listed in Microsoft's automatic-conversion guide. On Windows, look under File > Options > Data > Automatic Data Conversion. On Mac, use Excel > Preferences > Edit > Automatic Data Conversion.

When those values are identifiers, disable removal of leading zeros, keeping only the first 15 digits of long numbers, and conversion of digits surrounding E. These settings prevent conversions during operations such as opening files, typing and pasting; they won't reconstruct cells that have already changed. Microsoft explicitly says they don't directly affect Power Query, so you still need its column-type choices.

For a few references you're entering manually, Microsoft also documents formatting the empty destination cells as Text before entry, or typing an apostrophe before the identifier. That's an Excel entry convention, though. It isn't a reason to insert apostrophes into every source CSV field, where the characters should stay unchanged.

Compare the text, not just the way it looks

You need an intact source-reference column imported as Text to compare with the final reference column. Keep the original row order, or sort whole rows together so you don't end up comparing different transactions. The row count and any blanks need checking too: two empty cells aren't a successful match.

In a separate comparison sheet, put the intact source text in A2 and the candidate text in B2, then use =EXACT(A2,B2) in C2 and fill down. Microsoft's EXACT reference, updated July 24, 2026, says it compares text case-sensitively and ignores formatting differences. For this fictional fixture, all four preserved text pairs should return TRUE; comparing the originals with the four changed strings in the table should return FALSE throughout. This checks equality against your chosen source, not whether that source is correct.

A custom zero format can hide the problem rather than fix it. A six-place zero format can display the number 1234 as 001234, but the stored number is still 1234. That display matches the original only if you independently know the identifier was six digits. Otherwise, it could equally have started as 01234 or 0001234. The format supplies a pattern, not evidence of the missing characters, and no width rule can recover the lost 6 or 7 in the long-reference example.

CSV quotes do not declare a spreadsheet column type. The field grammar in RFC 4180, section 2 (October 2005), allows quoted and unquoted fields; neither carries an Excel Text type. That's why a new export needs checking as raw text, and why you need to import identifiers as Text again when using that CSV in Excel.

The CSV format repair tool gives you a separate place to review file structure. It can't recover digits missing from the file or force Excel's type choices when you later open a CSV. Once the references are intact, the bank CSV review checklist covers amounts, coverage and balances, because matching identifiers alone doesn't reconcile the books.

Moving the file to Sheets won't recover an absent digit

Google's Sheets number-formatting help documents Format > Number, including custom displays for zeros and exponents. The page is undated. Those controls change the display; they don't recover missing digits. Moving to Sheets still means starting with an intact source and checking the resulting identifiers character for character. A different spreadsheet won't repair the damaged Excel export.

If no intact CSV survives, you'll need to ask the bank or file provider for another export. A surviving original PDF may contain the full reference, in which case you can transcribe it into a Text column and check it against the page. A PDF that masks the number or omits the field can't supply the missing characters either. Without that evidence, the reference stays unresolved; inventing digits to make two records match isn't a repair.

What we checked

On October 17, 2026, we opened the linked Microsoft documentation, Google's formatting help and RFC 4180. Microsoft dates above are the pages' updated-at metadata, not claimed feature-release dates. We parsed the fictional CSV and used Python integers and Decimal to model the documented truncation, exponent conversion, lost-digit differences and display example. We also checked the expected text-comparison results. We didn't run Excel or Sheets, execute the worksheet formula, or test a live bank import. These checks concern identifier preservation, not statement authenticity or bookkeeping reconciliation.