The CSV Opened Cleanly, the Payment Run Totalled to the Penny, and Eighteen of Twenty References Had Already Lost Their Last Digit

The purchase ledger exports a payment run as remittance.csv. Someone double-clicked it, Excel opened, the twenty rows looked exactly as expected, and the file was saved as .xlsx and uploaded to the bank portal. Every payment went out on the right day, to the right supplier, for the right amount.

Eleven days later six suppliers were on the phone asking what a payment was for.

Nobody edited anything. The conversion happened between the double-click and the screen.

A CSV has no data types. It has commas and line breaks, and everything between them is characters. Something has to decide whether 00471382 is a number, a string or an account, and when you open a CSV by double-clicking it, that something is Excel's parser, working one field at a time with no schema and no questions. It is fast, it is usually right, and where it is wrong it is wrong silently and permanently — the original characters are not kept anywhere. There is no undo, because nothing was done to the workbook; the workbook was built this way.

What this covers. The parsing rules below are the same in Excel 2016, 2019, 2021, Microsoft 365 and Excel for Mac; the Automatic Data Conversion settings in section 9 exist only in Microsoft 365 and Excel 2024, which is the single most important version difference in this article. LEN, TEXT, VALUE, TRIM, LEFT, RIGHT, MID, COUNT, COUNTA, COUNTIF, COUNTIFS, SUMIFS, SUMPRODUCT, IFERROR, XLOOKUP, INDEX/MATCH, DATE, YEAR, MONTH, DAY, UNIQUE and LET work in every version unless noted; TEXTSPLIT, TEXTBEFORE and TEXTAFTER are 365 and 2024 only. A payment file is the example because the damage is easy to price, but this is the same job as a stock export, a payroll upload, a barcode list, a patient identifier, an ISIN, an IMEI or a gene name — anything where a string of digits or a short code carries meaning rather than magnitude.

1) The Sixteenth Digit

Excel holds a number as a 64-bit float and then deliberately limits it further: fifteen significant digits, and every digit after that is set to zero. Not rounded to, not displayed as — stored as. The bank's remittance format specifies a sixteen-digit reference, so every reference in this file is one digit longer than Excel can hold.

Eighteen of the twenty changed. The two that did not — PAY-4104 and PAY-4114 — are the two whose sixteenth digit was already a zero, so setting it to zero changed nothing. They survived by coincidence, not by being different.

in the workbook, where the references are now numbers:
=SUMPRODUCT(--(RIGHT(TEXT($E$2:$E$21,"0"),1)="0"))    → 20

against the source file, where they are still text:
=SUMPRODUCT(--(RIGHT($E$2:$E$21,1)="0"))              → 2

still matchable:  PAY-4104 + PAY-4114        =   5,950.45
not matchable:    228,122.35 - 5,950.45      = 222,171.90   97.39%

The two formulas differ for a reason worth keeping. RIGHT on a number reads that number's General display, and the General display of a sixteen-digit number is 4.02928E+15 — so =RIGHT(E2,1) returns 5, the last character of the exponent. TEXT(E2,"0") is what forces the digits out where RIGHT can see them.

222,171.90 of a 228,122.35 run — 97.39% — reached suppliers quoting a reference that appears in nobody's ledger. The largest single one is PAY-4103 at 31,275.00. The money arrived; the money always arrives, because the sort code, the account number and the amount are what move it. The reference is what tells the person receiving it which of their invoices it pays, and eighteen of them now point at nothing.

🎯 Scenario: Before you open any file of identifiers, ask one question of the longest one: how many digits? Sixteen-digit card numbers, sixteen-digit payment references, eighteen-digit database keys, twenty-digit IBANs and fifteen-digit IMEIs are all past the limit, and a column of them opened by double-click is already damaged. Sixteen is not a large number. It is one more than fifteen.

2) Every Check Passed Except One

The run was checked. Three ways, all of which passed.

"Does the total match the ledger?" To the penny:

=SUM($F$2:$F$21)          → 228,122.35     and the bank debit was 228,122.35
=COUNT($F$2:$F$21)        → 20             twenty lines in, twenty lines out
=COUNTBLANK($A$2:$F$21)   → 0              nothing dropped

Of course it did. Excel did not touch the amounts, because 18,400.00 and 2,145.60 are short enough to store exactly and are meant to be numbers anyway. The conversion damaged only the fields that were never quantities, and no arithmetic check can see damage in a column nobody adds up.

"Are all twenty suppliers present?" Yes — =COUNTA(UNIQUE($B$2:$B$21)) returns 6, the six suppliers the run was built from, and a SUMIFS by supplier reconciles to the ledger line by line. Column B is text with no numeric shape anywhere in it, so nothing happened to it.

"Does anything look wrong?" Column E displays as 4.02928E+15, which looks wrong in the harmless way — a formatting problem. Widen the column and it still says 4.02928E+15; set the format to Number with no decimals and it says 4029283751029380, sixteen digits, looking exactly like a reference should look. The visible symptom was fixed in one click, and fixing it hid the only outward sign that anything had happened.

Here is the check that fails, and the whole file contains one:

=SUMPRODUCT(--(RIGHT(TEXT($E$2:$E$21,"0"),1)="0"))    → 20

A digit lands on zero one time in ten. Twenty of them in a row is one in 10²⁰ — a hundred quintillion — which is not a thing that happens to a real file. Any column of identifiers where the last digit is always the same value has been through a conversion, and that single formula is the fastest way to find out.

🎯 Scenario: When you receive a file of identifiers, count the last digits before you use it — =SUMPRODUCT(--(RIGHT(TEXT($E$2:$E$21,"0"),1)="0")) against =COUNTA($E$2:$E$21)/10. If the first is far larger than the second, the file has been through Excel at some point in its life and the digits are already gone. Note that COUNTIF(E2:E21,"*0") cannot do this job: wildcards only match text, and after the conversion these are numbers.

3) The Account Column That Is Now Five Different Lengths

00471382 is eight characters in the CSV. Excel reads it as the number 471,382 and stores it as such, because a leading zero has no arithmetic meaning — and it is right about that, in the same way that a calculator is right that £00471382 is £471,382.

Five of twenty accounts, carrying 61,359.30 — 26.90% of the run. The column is now a mixture of five, six, seven and eight-digit numbers where every value should be an eight-character string, and the single formula that finds all of it is:

=SUMPRODUCT(--(LEN($D$2:$D$21)<>8))    → 5

Two things make this class of damage different from section 1, and both are worth knowing.

It is recoverable, because the rule is known: the field is eight characters, so =TEXT(D2,"00000000") puts the zeros back and gets 00471382 exactly. That works only because nothing but leading zeros was lost — pad a number that lost a trailing digit and you get a plausible, wrong answer.

And it is worse for lookups than for payments, which is the part people get backwards. The bank's field is fixed-width and left-padded, so most of these still reach the right account. What breaks is every match against another system: =XLOOKUP(D2,Accounts[Account],Accounts[Name]) compares the number 471382 against the text "00471382" and returns #N/A forever, because in Excel a number never equals a string that looks like it. Half the "Excel is broken" #N/A in the world is this.

🎯 Scenario: Any code that can start with a zero — account numbers, cost centres, UK postcodes in their outward part, French INSEE codes, US ZIP codes, product SKUs, phone numbers with a trunk prefix — is text, not a number, in every system it ever passes through. If your master file stores it as a number, the file is already lossy and every join against it is one TEXT() away from working.

4) The Codes That Became Dates

MAR-12 is a Marlow Fasteners part code. Excel reads it as the twelfth of March in the current year, converts it to the serial number 46093 and formats the cell as a date. SEP-4 becomes 46269, the fourth of September. OCT-3 becomes 46298.

This is the most famous data-conversion bug in the world, and it is the reason the HUGO Gene Nomenclature Committee renamed the human gene SEPT2 to SEPTIN2 in 2020: a fifth of published genetics papers with supplementary Excel files had gene names silently converted to dates, and the fix in the end was to rename the genes.

The next row is the detail that matters more than the conversion:

One column now holds text and dates side by side, from the same manufacturer, differing only in how big the part number happens to be. Sort it and the four dates go to one end. COUNTIF(C2:C21,"MAR-*") finds three of the four Marlow codes and misses MAR-12, which is no longer text and no longer starts with MAR. XLOOKUP against the parts master misses the same one. And the damage is not even consistent between machines, which is section 4's second half.

3/8 is a fitting size — three-eighths of an inch. On a machine set to English (United Kingdom) it becomes 46237, the third of August. On a machine set to English (United States) it becomes 46089, the eighth of March. The same file, opened by two people, produces two different values, neither of which is a size, and only one of them looks obviously wrong to either person.

=COUNT($C$2:$C$21)    → 5     numeric values in a column of part codes

A code column should return zero. Any other answer means some of your codes are no longer codes.

🎯 Scenario: =COUNT() over a column that should be entirely text is a one-cell audit for this whole family, and it costs nothing to leave permanently in a corner of any sheet that receives imports. Five numeric values in a column of part codes is not a rounding issue to look at later; it is five rows that will never match the master file again.

5) The E That Means Times Ten to the Something

2E5 is a Bexley Fittings code. Excel reads it as scientific notation — 2 × 10⁵ — and stores the number 200,000.

The rule is narrow and worth knowing exactly, because it explains why some codes survive and some do not. Excel converts a field to a number when it matches its scientific-notation shape: digits, the letter E (either case), an optional sign, and digits. 2E5 matches. 1E12, 3e4 and 5E-3 match. 2E5X does not. E5 does not, having no mantissa. 2F5 does not, because only E means this.

Two consequences follow, and the second is the nastier:

• Any identifier that happens to contain a lone E between digits can stop being an identifier. It bites product codes, chemical references, well names in oil and gas, and — notoriously — hexadecimal strings, where a hash like 3E9 is just a number now.
• The conversion is not reversible by eye, because 200000 could have come from 2E5, 2E05, 2.0E5 or from somebody typing 200000. Unlike a lost leading zero, there is no padding rule that puts it back.

🎯 Scenario: If your codes contain letters, check whether any of them is a bare E surrounded by digits, and check it in the source file rather than the workbook — by the time it is in the workbook the evidence is a number. One =SUMPRODUCT(--ISNUMBER(codes)) over the code column tells you how many stopped being codes.

6) Scientific Notation Is a Display; the Loss Is Not

These two things look identical on screen and are not the same event:

• Display: a number too wide for the column shows as 4.02928E+15. The value is intact. Widen the column, or format as Number, and every digit comes back.
• Loss: a number with more than fifteen significant digits has had its extra digits set to zero. The value is gone. Widening the column shows you sixteen digits, fifteen of which are the original ones.

The second is disguised by the first, and that is exactly how this file passed inspection. Somebody saw 4.02928E+15, recognised it as a well-known cosmetic annoyance, applied a number format, saw sixteen digits, and concluded it was fixed. It reads as fixed. =LEN(E2) even returns 16.

The only way to tell them apart inside the workbook is to compare against something that is not the workbook:

=IF(TEXT($E2,"0")=$H2,"same","LOST DIGIT")

— with the original reference pasted into column H as text (format the column as Text first, or the paste converts it too). If you no longer have the source file, the comparison cannot be made, and that is the real reason to treat an import as an event rather than a file operation: the source file is the only witness.

🎯 Scenario: Keep the raw download. Not the workbook you made from it — the original .csv, unopened, in a dated folder. It costs nothing and it is the only thing that can ever answer "what did this actually say?" Every check in section 13 is cheap; the source file is what makes them possible at all.

7) Where It Happens, and the Two Places It Does Not

The conversion is not about CSVs. It is about which door the text comes through.

Two entries deserve a note. Text to Columns is the one that surprises people, because it is a repair tool: run it on a column of codes, click Finish with the default General format, and it re-parses every cell exactly as the importer would have. The same dialog has a Text radio button in step 3 that prevents all of it, and skipping to Finish is the most common way a careful person does this damage to themselves on purpose.

Formatting the column as Text first works only for typing, and only before. Formatting a column as Text after the values are in it changes how existing numbers display and does not restore a single digit; the cell holding 471382 becomes the text "471382", still six characters.

🎯 Scenario: The apostrophe prefix is worth teaching to anyone who types codes: '0038 stays 0038, the apostrophe is not part of the value, and it survives copying. It does not survive a Text to Columns run on the same column afterwards, which is the trap in the trap.

8) The Import That Does Not Do This

Data ▸ Get Data ▸ From Text/CSV opens a preview window instead of a workbook. It does the same guessing by default — you can see it guessing in the preview — but it does it in front of you and lets you overrule it before a single value lands in a cell.

Three settings make the difference, in the Transform Data editor the preview's button opens:

1. Set every identifier column to Text. Click the column, then Transform ▸ Data Type ▸ Text. Do it for the reference, the account and the item code. In the formula bar this writes a Table.TransformColumnTypes step naming each column and its type.
2. Delete the automatic type step. Power Query adds a Changed Type step on import that types the columns by sampling the first two hundred rows. Delete it, or set Table.TransformColumnTypes yourself, and nothing is inferred from a sample — which also fixes the separate, sneakier problem of a column typed from rows 1–200 and then meeting something different at row 4,000.
3. Turn off type detection for good under Data ▸ Get Data ▸ Query Options ▸ Data Load, so new queries stop guessing by default.

The payoff is bigger than this one file. A query is repeatable: next month's remittance file lands in the same folder, you press Refresh, and the same typing rules apply with nobody remembering anything. A double-click is a decision made freshly, wrongly and invisibly every single month.

🎯 Scenario: If you receive the same shaped file more than once, never open it again. Build the query once, keep the workbook, and replace "open the file" with "press Refresh". The conversion in this article cannot happen to a file you never open.

9) The Four Checkboxes Microsoft Added in 2023

In Microsoft 365 and Excel 2024, File ▸ Options ▸ Data ▸ Automatic Data Conversion carries four boxes, all ticked by default:

• Remove leading zeros and convert to number
• Truncate numeric data to 15 digits
• Convert digits surrounding the letter "E" to scientific notation
• Convert continuous letters and digits to a number in scientific notation

Untick them and sections 3, 1 and 5 stop happening — to typed and pasted values immediately, and to opened CSVs as well if you also tick the fifth box below them, "Apply these settings when opening CSV files", which is separate and unticked by default. With the second box unticked a sixteen-digit reference typed or pasted in stays sixteen digits, and Excel shows a small prompt the first time rather than converting.

What these settings do not do: they are per-user, per-machine, stored in the registry rather than in the file, so they protect you and nobody you send the file to. They do not affect dates at all — MAR-12 still becomes a date, because Microsoft's list does not include that conversion. And they do nothing retroactively.

If your Excel does not have this menu, you are on 2021 or earlier and section 8 is the whole of your defence.

🎯 Scenario: Untick all four and the CSV box, on every machine that handles data files, the way you would set up a keyboard shortcut. Then keep using section 8 anyway, because the person who sends you next quarter's file has not read this article and is one double-click from sending you a pre-damaged one.

10) What Can Be Recovered and What Cannot

The dividing line is whether the lost characters can be derived from what is left.

The three "no" rows have one thing in common: Excel did not lose the data, it replaced it with a value that is indistinguishable from a legitimate one. That is why no repair formula exists and no error value ever appears. IFERROR cannot catch a number that is simply the wrong number.

Which leaves one recovery path that always works, and it is not a formula: go back to the source and import it properly. The ledger that wrote remittance.csv still knows all twenty references. The five minutes of section 8 is also the five minutes of the repair.

🎯 Scenario: When you find converted data, do not write a cleverer formula — re-import. Time spent reconstructing values from a damaged copy produces numbers that look recovered and cannot be proven, which is worse than the damage, because now they carry the authority of a formula.

11) The Round Trip

Save the workbook back as CSV and Excel writes what it now holds. 00471382 is written as 471382. 4029283751029384 is written as 4029283751029380. MAR-12 is written as whatever the cell's date format displays — 12/03/2026 on one machine and 3/12/2026 on another.

The file on the server is now the damaged file, and it looks exactly as authoritative as the one the ledger wrote. Anyone who compares it against the workbook will find perfect agreement.

This is the mechanism by which one careless double-click propagates through an organisation: a file is opened to "just have a look", closed with a Save prompt that everyone answers Yes to out of reflex, and the copy on the shared drive is silently replaced with a converted one. Nothing in the save dialog mentions it.

🎯 Scenario: Make data files read-only where you can, and answer No to the save prompt when you have only looked. A CSV you opened and did not intend to edit has nothing to save — and if Excel thinks it does, that is precisely the warning sign, because it means Excel changed something.

12) The Other Nine Conversions

The same parser does all of this on the way in, and every one of them is silent:

1. 1/2 becomes 2 January (a fraction needs 0 1/2, with the leading zero and a space).
2. (1,250) becomes −1250. Accounting exports use brackets for negatives and Excel agrees, which is convenient until a code in brackets goes negative too.
3. TRUE and FALSE become booleans, not text, and stop matching a lookup table that holds them as words.
4. +44 7700 900123 is a phone number to a person and the start of a formula to Excel: a leading +, - or = makes the field an expression, and = is how a CSV becomes a formula-injection vector in a spreadsheet that opens it.
5. 00:12 becomes a time — 0.008333 of a day — which is a nuisance when it was a duration in a log file and a disaster when it was a room number.
6. 1.250,00 in a file written by a German system becomes the text 1.250,00 on an English machine, or 1.25 if it is read as a decimal point; the decimal separator is regional and the file does not say which one it used.
7. Trailing and non-breaking spaces survive the import untouched, so "KS-4417 " never matches "KS-4417" and looks identical in both cells. =LEN() and TRIM are the whole diagnosis, and a non-breaking space (character 160) survives TRIM too.
8. A UTF-8 file without a byte-order mark gets read in the local code page, so accented supplier names arrive as é. Data ▸ From Text/CSV lets you set the encoding; double-clicking does not.
9. A number stored as text — the reverse case — does not convert on import when the field has a stray space or apostrophe, so it sums to zero and aligns left. =VALUE(TRIM(A2)) is the fix, and =SUMPRODUCT(--ISTEXT(F2:F21)) counts how many rows need it.

Three of those nine are recoverable, and they are the three where the original characters are still present and only the interpretation is wrong.

🎯 Scenario: Any field a human reads as an identifier — reference, code, phone number, room, postcode, part — is text to a computer. Say so at import time, once, and none of the twelve conversions in this article can happen to it.

13) Four Checks

One cell each, on any sheet that receives imported data.

=SUMPRODUCT(--(LEN($E$2:$E$21)>15))
    How many identifiers are longer than Excel can store. Run this against
    the source file before opening it — 20 here — because afterwards every
    one of them is exactly 16 characters long and this check returns 20
    whether or not the digits are still the right ones.

=SUMPRODUCT(--(RIGHT(TEXT($E$2:$E$21,"0"),1)="0"))
    Identifiers whose last digit is zero. Expect around a tenth of the rows.
    20 out of 20 is the signature of a truncation that has already happened.

=SUMPRODUCT(--(LEN($D$2:$D$21)<>8))
    Fixed-width codes that are no longer the fixed width. 5 here, all of
    them leading zeros removed. The one class of damage TEXT can undo.

=COUNT($C$2:$C$21)
    Numeric values in a column that should be entirely text. 0 in a clean
    file; 5 here — four dates and one 200,000 that used to be 2E5.

All four test a property of the column that should be invariant — every value the same length, every value text, last digits spread evenly — rather than checking values against a source you may not have. That is what makes them runnable on a file you were simply handed.

14) Twelve Traps

1. Excel stores fifteen significant digits and zeroes the rest. Not rounds — zeroes. A sixteen-digit reference is one digit past the limit and loses that digit on the way in.
2. The damage is invisible to every count, total and length check. The corrupted reference is still sixteen characters, still unique, still a number. Only the distribution of the last digit gives it away.
3. Fixing the scientific-notation display hides the only visible symptom. 4.02928E+15 formatted as a number shows sixteen digits, fifteen of them original.
4. Leading zeros are removed on import and on typing, and formatting the column as Text afterwards does not bring one back.
5. A number never matches text that looks like it. 471382 against "00471382" is #N/A forever, and it is the most common cause of a lookup that "should work".
6. A code that looks like a day becomes a date, and its longer sibling does not. MAR-12 converts, MAR-118 does not, and one column ends up holding two data types from one manufacturer.
7. Two-digit numbers above 31 are read as years. MAR-45 is 1 March 2045, not a part code and not a day.
8. Slashed codes are regional. 3/8 is 3 August in London and 8 March in Chicago, so the same file holds different values on different desks.
9. A lone E between digits means scientific notation. 2E5 is 200,000, and 200,000 has four plausible originals, so nothing puts it back.
10. Text to Columns re-runs the whole conversion on a column that was already safe. It is a repair tool that does this damage, and the Text radio button in step 3 is the only thing standing in the way.
11. Saving back to CSV writes the damage to disk. The converted file replaces the original and carries the same authority.
12. The Automatic Data Conversion settings are per-machine and do not cover dates. They protect you, not the person who sends you the file, and MAR-12 converts with every box unticked.

Nothing in this story is a bug. Every conversion Excel performed is the conversion a spreadsheet should perform on a field that looks like that, and each one in isolation is a small kindness: nobody wants to type =DATE(2026,3,12) when 12/3 will do, or see a leading zero on a quantity.

What makes it expensive is that a CSV arrives with its meaning stripped off. The ledger knew that column E was a reference and column D was an account; the comma-separated file it wrote knows only that there were commas. Excel then reconstructs the meaning by looking at the characters, which is the best anybody could do with what it was given, and it is wrong for exactly the fields where the characters are the meaning rather than a way of writing a quantity.

So the fix is never a better formula. It is telling something, once, at the point of import, that these digits are a name and not a number — and then never opening the file any other way again.