Stop Excel Turning Long Numbers into 4.5E+15

You open an export of order references in Excel and the first cell reads 4.53212E+15. Someone tells you to widen the column or change the number format. You do, and the number comes back as 4532118899001230 — close to the original, but the last digit is wrong, and no amount of reformatting will fix it.

These Are Two Separate Problems

4.53212E+15 in a cell can mean two very different things, and it matters a great deal which one you are looking at.

Microsoft documents both. The support page on scientific notation states that the General format “does use exponential notation for large numbers (12 or more digits)”, and that “the maximum limit for number precision is 15 digits, so the actual value shown in the formula bar might change for large numbers (more than 15 digits)”. The Excel specifications page lists number precision as 15 digits flat. Microsoft’s explanation of why is unusually direct: Excel “was designed around the IEEE 754 specification”, and although it can store numbers up to about 1.8E+308, “it can only do so within 15 digits of precision”. Its page on keeping large numbers spells out the consequence — for any value of 16 or more digits, “such as a credit card number, any numbers past the 15th digit are rounded down to zero”.

So a 13-digit barcode that displays as 4.00638E+12 is only a display problem — click the cell and the formula bar still shows all 13 digits. A 16-digit card reference is a data-loss problem: the formula bar itself now shows a zero where your last digit used to be.

Which Real Values This Destroys

Anything longer than 15 digits that is written with digits only. The common ones:

An IBAN normally survives, because it begins with a two-letter country code and Excel will not read letters as a number; the same accident protects an order ID like ORD-000123456789012345. It is the pure-digit columns that are at risk — though letters are no guarantee either, since Excel has a separate setting that reads digits around the letter E as an exponent, so a part number written 123E5 becomes 12,300,000.

How to Check Whether a File Is Already Damaged

1. Click the cell and read the formula bar, not the cell. The cell shows the format; the formula bar shows the value Excel actually holds. If the formula bar ends in zeros that should not be there, the value is gone.
2. Check the length. Put =LEN(A2) next to the column. Card numbers, IMEIs and SSCCs have fixed lengths, so anything shorter than expected is suspect.
3. Look for a wall of trailing zeros. Genuine identifiers rarely end in three or four zeros. A whole column that does is the signature of 15-digit truncation.
4. Open the CSV in a text editor instead. The file is plain text, so it shows exactly what was written. A file that was opened in Excel and saved again contains Excel’s version of each value, not yours: at best the rounded 16-digit number, sometimes the exponential form itself. Either way the original digits are not in there. See how to open a CSV that is too large for Excel if the file is big.
5. Test the check digit. Card numbers, IMEIs and GS1 codes all end in a check digit calculated from the digits before it. If check digits stop validating for a whole column, the column has been rewritten.

If the file is damaged, do not try to reconstruct it. Go back to the original export, or ask the sender for it again.

Fix 1: Turn the Conversion Off

Excel for Microsoft 365 and Excel 2024 let you switch the automatic conversions off, at File › Options › Data on Windows or Excel › Preferences › Edit on a Mac. The checkbox that matters here is Keep first 15 digits of long numbers and display in scientific notation: untick it and a value of 16 or more digits is stored as text with every digit intact, instead of being cut to 15. Worth ticking at the same time is When loading a .csv file or similar file, notify me of any automatic data conversions, which at least tells you when something has been changed. How to convert a CSV to Excel without breaking your data covers all four switches and which versions have them. If your Options has no Automatic Data Conversion section, use Fix 2.

Fix 2: Import the File with the Column Typed as Text

This works in every modern Excel version, and it is the route Microsoft itself recommends for credit card numbers: open a blank workbook, go to Data › From Text/CSV, press Transform Data, then set each long-number column to Home › Transform › Data Type › Text and choose Replace current. The import walkthrough has the full sequence, including the dropdown that turns type detection off for every column at once.

The one thing that catches people is order. Power Query adds a Changed Type step of its own based on the first 200 rows, and if that step has already turned the column into a number, converting it to text afterwards only formats the damaged value. Set the type where types were first detected, or delete the automatic Changed Type step.

If your Excel has no From Text/CSV button, the old Text Import Wizard still exists: switch on Show legacy data import wizards under File › Options › Data, then use Data › Get Data › Legacy Wizards and set the column format to Text in step 3. Microsoft also notes a shortcut for forcing that wizard: “you can change the file name extension from .csv to .txt before you open it.”

Fix 3: Format as Text Before You Type or Paste

If you are building a sheet by hand or pasting from another window, format the destination cells first: Home › Number Format › Text, then paste. A text-formatted cell holds up to 32,767 characters, so length is never the problem. For one-off entries, typing a single apostrophe before the number — '4532118899001234 — has the same effect. Both must happen before the value arrives in the cell.

Why Reformatting Afterwards Cannot Help

A number format is only a mask over a stored value; as Microsoft puts it, a number format “doesn’t affect the actual cell value that Excel uses to perform calculations”. Widening the column, choosing Number with zero decimals or applying a custom format of 0 all change the mask. If the value underneath still has all its digits — anything up to 15 — that is a complete fix. If Excel has already stored 4532118899001230, there is nothing to reveal. Excel did not hide the last digit; it replaced it.

CSV Never Had This Problem

Worth saying clearly, because it changes where you look for the fault: the CSV file is almost never wrong. A CSV is plain text, so 4532118899001234 in a file is sixteen characters, the same way Wednesday is nine. There is no number type, no precision limit and no rounding. Every problem in this guide is created by the program reading the file, which is why one export can be perfect in a text editor and in a database import, and wrong only in Excel.

Doing the Job without Excel

Most long-number damage happens when someone opens a CSV in Excel purely to perform a mechanical task, then saves it again. If that is your situation, skip the spreadsheet. Our tools read and write CSV text and copy each row as it stands, so a 19-digit card reference comes out with 19 digits: split by column value, remove duplicate rows, merge files and change the encoding or delimiter. They do not make .xlsx files, do not open Excel workbooks, and do not edit cells, chart or sort — they are no substitute for a spreadsheet when you actually need to calculate something.

When you do need to work with the data rather than move it, two free routes keep every digit. DuckDB reads a CSV with SQL and takes read_csv('file.csv', all_varchar = true), which skips type detection entirely. LibreOffice Calc shows a column-by-column import dialog every time it opens a CSV, where any column can be set to Text — the detail is in how to convert a CSV to Excel without breaking your data.

The sibling problem is leading zeros, which Excel strips from ZIP codes and employee numbers by the same guesswork: how to stop Excel removing leading zeros. If the identifiers are being combined from several exports, do the merge as text first and only then open the result, because one spreadsheet round trip anywhere in the chain undoes the care taken everywhere else: how to merge CSV files that have different columns.