🧹 Data Cleaning · 9 min read
CSV Scientific Notation: Why Excel Rewrites Long Numbers
If a long order number, account code or tracking ID opened as something like 1.23E+15, the CSV file is almost certainly fine. Excel converted that column to a number when it opened the file, and once a column is numeric the digits are subject to Excel's own precision rules.
The conversion is documented rather than mysterious. Microsoft's support article on keeping leading zeros and large numbers states that Excel "automatically removes leading zeros, and converts large numbers to scientific notation, like 1.23E+15, in order to allow formulas and math operations to work on them". The same article lists a conversion that matters more than the display, which is truncating numerical data to 15 digits of precision.
Quick answer
Set the column type to Text during import, not after it. In Excel that means Data, then From Text/CSV, then Edit in the preview pane, then select the column and choose Home, Transform, Data Type, Text, and Replace Current. Formatting the column as text once the data is already on the grid is the step people try first, and it is the one that cannot work, because the conversion has already happened.
What Excel actually does when it opens a CSV
Excel reads a CSV as untyped text and then guesses a type for every column. That guessing is the feature: it is why a CSV of sales figures opens with numbers you can sum instead of strings you cannot. It also has four consequences that Microsoft documents in its automatic data conversions list:
- Leading zeros are removed and the value becomes a number.
- Numerical data is truncated to 15 digits of precision and displayed in scientific notation, so the sixteenth digit and everything after it stops existing inside the workbook.
- Text built around the letter E is read as scientific notation.
- A continuous string of letters and numbers is converted to a date, which is why part codes such as 1E10 or 12MAR2026 can arrive looking like something else entirely.
The distinction that decides how much trouble you are in is whether the file was ever saved from Excel. Opening a CSV and looking at it changes nothing on disk. Opening it and saving it writes the workbook's values back out, and a value that has been truncated to 15 digits is saved as the truncated value. That is the moment the original digits are lost, and it is why the fix has to happen at import rather than afterwards.
How to tell whether the file or the spreadsheet is the problem
Check the file first, because that decides which fix you need. Open the CSV in any viewer that does not apply column types, or read the first few lines as text, and look for the long value. If it is intact in the file, you are dealing with an import setting. If it is already E+ notation in the file, the damage happened at export or at a previous save, and you need the source system rather than Excel. Our guide to opening a CSV in a viewer covers the first check, and a quick look at the raw header and first row with the CSV analyzer tells you what the column really contains before a spreadsheet gets a say.
Fix 1: declare the column as Text in the import
This is the fix that holds, and the steps are Microsoft's own. Go to the Data tab and choose From Text/CSV next to the Get Data button. Excel loads a preview of the file. Press Edit in the preview pane to open the Query Editor, click the column header of the column that must stay text, then Home, then Transform, then Data Type, then Text. In the Change Column Type dialog choose Replace Current. Select Close & Load when you are done, and the data arrives in the worksheet as text.
The part worth knowing is what happens later. Microsoft notes that if the underlying data changes you can use Data, then Refresh, and Excel will update the data and reapply your transformations. The declaration is part of the query, not a one-off click, so the same file re-imports correctly next month without you remembering anything.
Fix 2: switch the automatic conversion off
Microsoft exposes the guesswork as a setting in Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024 and Excel 2024 for Mac. From there you can change the default behaviour for removing leading zeros, for truncating numerical data to 15 digits and showing it in scientific notation, for reading values around the letter E as scientific notation, and for turning letter-and-number strings into dates.
On Excel 2021 or older, and on most web and mobile builds, that setting is not available. In those versions the import route above is the only reliable one, which is why the fix worth learning is the one built into the import rather than the one hidden in options.
Fix 3: keep the value readable inside the workbook
If the code only has to survive inside one workbook, Microsoft documents a custom or special number format that keeps number codes intact, and notes that it works for codes containing fewer than 16 digits. Read the limit honestly: a 17-digit value is past the precision Excel stores, so no cell format can restore digits the application never kept.
This route also leaves the column as a number, which means a later export or pivot treats it as one. Use it for a report you are reading, not for a file you are handing to another system.
What does not work
- Renaming the file or changing the extension. CSV has no place to store a column type, so there is nothing for Excel to read even if the name says text.
- Formatting the column as Text after the data has landed. The values are already numbers, and the format only governs what happens next.
- Converting to .xlsx and straight back to CSV. You get a smaller number out than you put in, because the workbook is the layer that truncated it.
- Editing the E+ cell by hand. Typing over it can work for one record and will not survive a refresh or a re-import.
Symptom to cause to fix
| What you see | What happened | Fix |
|---|---|---|
| 1.23E+15 instead of a 16-digit ID | Column was typed as a number and displayed in scientific notation | Re-import with the column set to Text |
| Last digits changed to zeros | Precision limit: the value was truncated to 15 digits | Re-import as Text, or restore from the source system if the file was saved |
| A part code turned into a date | Letter-and-number strings are converted to dates automatically | Same import route, column set to Text |
| Value looks right in a viewer but wrong in Excel | The file is intact; the spreadsheet is doing the converting | Adjust the import, leave the file alone |
| Every value in the file shows as E+ notation | The export or a previous save already wrote the converted values | Go back to the source system; Excel cannot rebuild the digits |
Two neighbours of this problem are worth knowing, because they arrive in the same file and look similar. Excel stripping leading zeros is the same conversion aimed at ZIP codes and phone numbers, and our leading zeros guide covers it. Columns that collapse into one are a delimiter or encoding problem rather than a type problem, and that is the one-column diagnosis.
Frequently asked questions
Is the scientific notation stored inside my CSV file?
No. A CSV file is plain text, and the characters in it are whatever your export wrote. Excel applies its own number formatting when it opens the file, so 1.23E+15 on screen is a decision your spreadsheet made, not something the file contains. Microsoft describes this as an automatic data conversion: Excel converts large numbers to scientific notation so that formulas and math operations keep working.
How many digits before Excel changes a number to scientific notation?
Two different things are happening, and Microsoft documents them separately. The data loss limit is precision: Excel truncates numerical data to 15 digits of precision when it converts a value to a number. The E+ display can appear earlier than that for a plain display reason, because a column that is too narrow switches to scientific notation instead of widening itself.
How do I stop Excel from converting long numbers to scientific notation?
Set the column type to Text during import, before the data reaches the grid. In the Data tab choose From Text/CSV, load the file into the preview pane, press Edit to open the Query Editor, select the column, then Home, Transform, Data Type, Text, and choose Replace Current before Close & Load. Microsoft documents that later refreshes reapply the same transformation.
Can I turn the automatic data conversion off permanently?
Yes, in Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024 and Excel 2024 for Mac. Microsoft lists a single setting that controls several of these conversions at once, including removing leading zeros, truncating numerical data to 15 digits of precision and displaying it in scientific notation, and converting a continuous string of letters and numbers to a date. In Excel 2021 and older there is no such setting, so import as text instead.
Does formatting the column as Text after import bring the digits back?
No. The conversion happens on entry, and Text formatting only changes how Excel treats values entered from that point on. If the digits are already gone, restyle the column and then re-import from the original file with the column set to Text during the import step.
Is there a way to keep the value readable without changing the file?
Yes, as long as the code is short enough. Microsoft documents a custom or special format for keeping number codes intact inside a workbook, and notes that it works for codes containing fewer than 16 digits. That is a workbook-only fix: it changes how the value looks and stays in Excel, but the underlying figure is still a number that other programs may read differently.
Why did other tools save the value correctly but Excel did not?
Because the conversion is Excel's, not the format's. Database exports, payment platforms and scripts write CSV as plain text and do not apply column types. The moment a program decides that a column is numeric, it inherits that program's precision rules, which is why the same file can look right in a viewer and wrong in a spreadsheet.
Is this the same problem as Excel removing leading zeros?
It is the same mechanism applied to a different symptom. Microsoft describes both under one article on keeping leading zeros and large numbers: Excel strips leading zeros, and converts large numbers to scientific notation, for the same reason, so that the values behave as numbers. The fix is also the same, which is to declare the column as text at import time.
Tools mentioned in this guide
The check that separates a file problem from a spreadsheet problem takes a minute if you have the right three things to hand:
- OpenCode Go — writing a five-line script to print the first three lines of a CSV as text is faster than fighting an import dialog, and that is the check this article opens with. Try OpenCode Go
- Stack AI — if the cleaned file has to land somewhere as a recorded step, a workflow can read the CSV, keep the code column as text and write the row somewhere you can audit later. Try Stack AI
- Softr — for a catalogue of codes that people have to look up, a no-code app gives each record its own page, which is one way to stop long IDs living only inside spreadsheets. Try Softr
Some links above are affiliate links — if you buy through them we may earn a commission at no extra cost to you. OpenCode Go uses our referral link; the other two currently point to each vendor's official page until our tracking links are approved.
Need the File Checked Before It Reaches a Spreadsheet?
Paste the first rows into the analyzer and read the columns as they actually are, without a spreadsheet deciding what they mean.
Sources for this article: Microsoft Support, keeping leading zeros and large numbers, and the automatic data conversions reference it links to. Both were checked on the day this page was published.
Related reading
Data Cleaning — other guides that pair well with this one.
Browse all guides in the NoCodeCSV blog.