How to Fix Scientific Notation in Excel After a CSV Import

To fix scientific notation in Excel after a CSV import, re-import the file and set the affected column to Text before loading: in Data > Get Data > From Text/CSV, open Transform Data, select the ID column, and change its type to Text. If the file is already open and the digits are already wrong, re-importing is the only real fix — widening the column or changing the format cannot bring back digits Excel has already discarded.

Scientific notation looks like a display problem, but it is really a data-loss problem. A 16-digit account number such as 1234567890123456 becomes 1.23E+15 on screen, and behind the scenes Excel has already rounded it to 1234567890123450. The last digit is gone. Understanding that distinction is the key to fixing it correctly.

Why Long Numbers Become 1.23E+15

Excel stores numbers as double-precision floating-point values. That format can represent about 15 significant digits exactly. Any digits beyond the 15th are replaced with zeros. This is not a bug — it is how Excel's number system works, and it is fine for ordinary math. It is not fine for identifiers.

When Excel opens a CSV, it guesses each column's type. A column of 16-digit numbers looks like numbers, so Excel converts them to numeric values. Two things then happen:

1. Display: The number is too long for the column width, so Excel shows it in scientific notation: 1.23E+15.
2. Precision loss: The 16th digit (and any beyond) is rounded to zero. The stored value is no longer the original number.

This affects any long numeric-looking identifier: bank account numbers, credit card numbers, product barcodes, national ID numbers, phone numbers with country codes, order IDs, and tracking numbers. It does not affect ordinary amounts, quantities, or measurements, which rarely exceed 15 significant digits.

The Critical Rule: Widening the Column Does Not Recover Digits

The most common reaction to 1.23E+15 is to widen the column or change the number format to 0. That fixes the display — and reveals the damage. The widened column shows 1234567890123450, not the original 1234567890123456. The last digit was lost at import time, and no formatting change can restore it.

The same applies to the TEXT function, custom formats, and copying the values elsewhere: they all operate on the already-rounded stored value. Once Excel has converted the text 1234567890123456 to the number 1234567890123450, the original string is gone from the workbook.

This is why the fix must happen at import time, before Excel converts the text to a number. If the file is already open with rounded values, the only correct fix is to re-import the original CSV with the column set to Text.

Diagnose First: Display Problem or Data Loss?

Before fixing, find out which situation you are in:

1. Widen the column (double-click the column border) and look at the full value.
2. Compare against the source. Check one ID against the original CSV (open it in a text editor such as Notepad) or the source system.
3. Count the digits. If the widened value ends in zero where the source has a non-zero digit, precision was lost and you must re-import.
4. Check the cell format. Select a cell and look at the Home tab: if the format is General or Number and the value is rounded, the damage is done.

If the widened value matches the source exactly, you only have a display problem, and a format change is enough. If it does not match, re-import.

Method 1: Import the Column as Text (Best Fix)

Importing as Text tells Excel to treat the column as strings, not numbers. The digits are preserved exactly, including any leading zeros.

Step-by-Step: Get Data Import as Text

1. Start with a blank workbook. Do not open the CSV directly.
2. Open Get Data. Go to Data > Get Data > From File > From Text/CSV and select your CSV file.
3. Open Transform Data. In the preview dialog, click Transform Data to open Power Query Editor.
4. Set the ID column to Text. Select the ID column, then go to Transform > Data Type > Text. The preview should now show the full digit string, not scientific notation.
5. Check other columns. Leave amount and quantity columns as numbers — only identifier columns need Text. A column such as a phone number, account number, barcode, or order ID is an identifier even if it looks numeric.
6. Close and load. Click Close & Load. The IDs load as text strings with every digit intact.

Step-by-Step: Legacy Text Import Wizard

In older Excel versions, or when you prefer the classic wizard:

1. Open a blank workbook and go to Data > From Text/CSV (or Data > Get External Data > From Text).
2. Select the file and step through the wizard.
3. In step 3, set the ID column to Text. Click the ID column in the preview, then choose Text as the column data format.
4. Finish and load. The IDs arrive as text with full precision.

Both routes work. The Get Data route is better for recurring imports because Power Query saves the Text step and applies it on refresh.

Method 2: Pre-Format Columns as Text Before Pasting

If you are pasting CSV data into an existing workbook rather than importing a file:

1. Select the destination columns where the IDs will go.
2. Format them as Text (Home > Number format > Text) before pasting.
3. Paste the data. Use Paste Special > Text if your version offers it, or paste normally — the pre-formatted Text cells will keep the digits as strings.

This method works for copy-paste workflows, but it is easy to forget the pre-formatting step. For file imports, Method 1 is more reliable because the Text type is set during import, not before it.

Method 3: Fix an Already-Damaged File (Re-Import Only)

If the workbook is already open with rounded IDs, do not try to repair the values in place. There is nothing to repair — the original digits are not in the workbook.

1. Go back to the original CSV. Open it in a text editor to confirm the original digits are still there.
2. Re-import with Method 1, setting the ID column to Text.
3. Re-apply any edits you made to the damaged workbook, or copy the corrected ID column into the edited workbook (as Text) if the edits were extensive.

If the original CSV is gone and only the damaged workbook remains, the lost digits cannot be recovered from Excel. You will need to re-export from the source system — a bank, a shop platform, or a database — and import correctly. This is why keeping the original CSV as a backup matters.

For shop data, the guide to converting WooCommerce orders CSV to Excel covers re-exporting order data, and the guide to converting a payroll CSV to Excel covers handling sensitive ID columns locally.

Preventing Scientific Notation on Future Imports

• Identify identifier columns in advance. Before importing, list the columns that are identifiers (IDs, account numbers, barcodes, phone numbers) rather than quantities. Set those to Text.
• Use a repeatable import. Save the import as a Power Query query or a documented wizard sequence, so the Text step is applied every time. The guide to combining multiple CSV files with Power Query shows how Power Query keeps repeated imports consistent.
• Ask the source for quoted IDs. If you control the export, some tools can wrap IDs in quotes, which signals to importers that they are text. Not all importers respect quotes, so still set the column to Text.
• Never double-click a CSV with long IDs. Double-clicking lets Excel guess every column type, and it will guess Number for ID columns. Always import deliberately.

Comparison Table: Which Fix Should You Use?

Importing as Text is the only fix that preserves the digits. Format changes, TEXT(), and widening the column only change the display of an already-rounded value.

Common Mistakes With Scientific Notation

Widening the Column and Calling It Fixed

Widening reveals the rounded value (…450 instead of …456). It does not restore the lost digit. Always compare against the source after widening.

Changing the Format to 0

Setting the number format to 0 shows all 15 digits plus zeros, but the digits beyond the 15th are still zeros. The display looks complete, which makes this mistake especially dangerous.

Using TEXT() on a Rounded Value

=TEXT(A2, "0") formats the stored value. If the stored value is already rounded, TEXT() returns the rounded digits. It cannot recover what Excel discarded.

Sorting or Filtering on Rounded IDs

Two different IDs that round to the same 15-digit value (for example, …456 and …457 both becoming …450) will sort together and match each other in lookups. This causes duplicate and mismatch errors downstream. Fix the import before any lookup or deduplication.

Treating Phone Numbers as Numbers

Phone numbers are identifiers, not quantities. Import them as Text, or a leading + and leading zeros will be lost along with the digits. The same applies to ZIP codes, which the split-CSV guide flags as a leading-zero risk.

Frequently Asked Questions

Why does Excel show 1.23E+15 instead of my full number?

Because Excel stores numbers with about 15 significant digits of precision. A longer number is rounded, and when the column is too narrow to show the result, Excel displays it in scientific notation. The notation is a symptom; the rounding is the real problem.

How do I stop Excel from converting long numbers to scientific notation?

Import the CSV deliberately and set the ID column to Text before loading — in Get Data > Transform Data, or in step 3 of the legacy Text Import Wizard. Text columns preserve every digit exactly.

Can I recover the lost digits from a file that already shows 1.23E+15?

No, not from the workbook itself. The original digits were discarded at import time. Re-import the original CSV with the column set to Text, or re-export from the source system. If neither is available, the digits are unrecoverable.

Does the same problem affect Google Sheets?

Yes. Sheets also uses double-precision numbers and shows long values in scientific notation. The fix is the same: import the column as plain text, or format the column as plain text before pasting, so the digits are stored as strings.

Should I store IDs as numbers or text in Excel?

As text. IDs are labels, not quantities — you never add or average them. Storing them as text preserves every digit, keeps leading zeros, and avoids lookup mismatches caused by rounding. Amounts, quantities, and measurements stay as numbers.

Final Checklist

Before you share a file with long ID numbers, run through this short checklist:

• ID columns are stored as Text, not Number or General.
• A widened ID column matches the source CSV digit for digit.
• No ID ends in a suspicious zero where the source has a non-zero digit.
• Phone numbers kept their + prefix and leading zeros.
• The original CSV is saved as a backup.
• Future imports will set ID columns to Text at import time.
• Lookups and deduplication run only on the corrected Text IDs.

If all seven checks pass, your IDs are intact — and the next import will not silently round them away.