Data Import & Export

How to Prevent Excel from Replacing Long Numbers with Zeros

Bushra ParveenBushra Parveen Nov 4, 2026 870 views

Question details

The user needs to stop Excel from converting long numbers (over 15 digits) into scientific notation and replacing the trailing digits with zeros.

How to Prevent Excel from Replacing Long Numbers with Zeros
Product
Microsoft Excel
Device & OS
Windows
Scenario
Importing or entering long numerical identifiers such as barcodes, order IDs, or credit card numbers from a CSV file or manual input.
Observed behavior
Excel truncates numerical values after 15 significant digits, replacing the remaining digits with zeros and often displaying the number in scientific notation.
Before you start

Remember that once Excel has converted a number and replaced trailing digits with zeros, the original data is permanently lost in that file. You must define the format as text before entering or importing the data to preserve the exact digits.

Solution 1Recommended

Import CSV Data as Text Using Power Query

The best way to prevent data loss when opening CSV files is to explicitly define columns containing long identifiers as Text during the import process.

Opening a CSV file directly by double-clicking it allows Excel to automatically apply general formatting, which triggers the 15-digit limit. Instead, you should import the data using Excel's built-in tools.

1
Open the Data tab

Open a blank workbook, navigate to the 'Data' tab on the Excel ribbon, and click 'From Text/CSV'.

2
Select the file

Locate and select your CSV file containing the long numbers, then click 'Import'.

3
Transform data

In the preview window that appears, click 'Transform Data' to open the Power Query Editor.

4
Change data type to Text

Select the column with the long identifiers, click the data type icon in the column header (usually displaying '123'), and choose 'Text'. Click 'Replace current' if prompted.

5
Load the data

Click 'Close & Load' in the top-left corner to bring the data into your Excel worksheet safely without losing precision.

Import CSV Data as Text Using Power Query
Data Preservation: Importing the column as text ensures that Excel treats the values as characters rather than math inputs, perfectly preserving every digit.
Seamless Data Management

Easily Import and Manage Long Numbers with WPS Spreadsheet

WPS Spreadsheet provides an intuitive Text Import Wizard that automatically detects CSV files and lets you easily format columns as Text before they load, ensuring you never lose critical digits from your long identifiers.

  1. 1. Open WPS Spreadsheet: Launch WPS Office and open WPS Spreadsheet.
  2. 2. Open the CSV file: Go to 'Menu' > 'Open' and select your CSV file.
  3. 3. Use the Import Wizard: The Text Import Wizard will automatically pop up. Proceed to the step where you can set column data formats.
  4. 4. Set column format to Text: Click on the column containing the long numbers in the preview window, and select 'Text' under the Column data format options.
  5. 5. Finish the import: Click 'Finish' to successfully import your data with all digits perfectly preserved.
Free to download and useFully compatible with Microsoft Excel formats (.xlsx, .csv)Intuitive Text Import Wizard prevents accidental data lossLightweight and runs smoothly on Windows, Mac, and Linux
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel change my 16-digit credit card number to end in zero?

Excel adheres to the IEEE 754 specification for floating-point numbers, meaning it can only store up to 15 significant digits. Any digits beyond the 15th are automatically converted to zeros to prevent calculation errors. To avoid this, you must store the number as text.

Can I recover the lost digits after Excel has replaced them with zeros?

No. Once Excel has converted the number and replaced the trailing digits with zeros, the original data is permanently lost. You cannot restore it by changing the format after the fact; you must re-import or re-enter the original data using the Text format.

How do I fix scientific notation (e.g., 1.23E+15) appearing instead of my number?

If the number has 15 digits or fewer, you can simply widen the column or change the cell format to 'Number' instead of 'General'. If it has more than 15 digits, widening the column will reveal the zeros at the end. In that case, you must format the cell as Text before entering the number.

Does this 15-digit limit affect formulas and calculations?

Yes, the 15-digit precision limit applies to all calculations in Excel. If a formula calculates a result that exceeds 15 significant digits, Excel will truncate the remaining digits and replace them with zeros.