How to Prevent Excel from Replacing Long Numbers with Zeros
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.

- 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.
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.
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.
Open a blank workbook, navigate to the 'Data' tab on the Excel ribbon, and click 'From Text/CSV'.
Locate and select your CSV file containing the long numbers, then click 'Import'.
In the preview window that appears, click 'Transform Data' to open the Power Query Editor.
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.
Click 'Close & Load' in the top-left corner to bring the data into your Excel worksheet safely without losing precision.

Format Cells as Text Before Manual Entry
If you are pasting or typing data manually, formatting the destination cells as Text beforehand prevents Excel from applying its numeric limits.
Use an Apostrophe for Single Cell Entries
For quick, one-off entries, you can force Excel to treat a number as text by adding a single quote mark at the beginning of your input.
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. Open WPS Spreadsheet: Launch WPS Office and open WPS Spreadsheet.
- 2. Open the CSV file: Go to 'Menu' > 'Open' and select your CSV file.
- 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. 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. Finish the import: Click 'Finish' to successfully import your data with all digits perfectly preserved.

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.




