Data Import & Export

How to Stop Excel from Changing the Last Digit of a 16-Digit Number to Zero

WPS Editor Sep 28, 2026 869 views

Question details

The user needs to prevent Excel from automatically changing the final digits of long numeric sequences (16 digits or more) into zeros when entering data.

How to Stop Excel from Changing the Last Digit of a 16-Digit Number to Zero

Product: Microsoft Excel
Device & OS: not provided
Scenario: Entering 16-digit strings, such as credit card numbers or identification numbers, into spreadsheet cells.
Observed behavior: When entering a 16-digit number, Excel converts the 16th digit to a zero because it only supports 15 significant digits for numeric values.

Before you start

Ensure you have not already saved the incorrect numbers, as digits automatically changed to zero by Excel cannot be restored and must be re-entered manually.

Solution 1Recommended

Use an Apostrophe Before Entering the Number

This is the quickest method for single entries to force Excel to treat the number as text.

Excel is designed to handle up to 15 significant digits accurately. Any digit after the 15th is automatically rounded to zero. By typing a single apostrophe (') before your number, you instantly convert the cell's format to text, preserving all digits.

Select the Cell

Click on the specific cell where you want to enter your 16-digit number.

Type an Apostrophe

Press the apostrophe key (') on your keyboard. This acts as a hidden formatting trigger.

Enter Your Long Number

Immediately type your 16-digit number after the apostrophe, for example: '1234567890123456.

Press Enter

Hit the Enter key. The apostrophe will remain hidden in the sheet view, and your number will display exactly as you typed it.

Use an Apostrophe Before Entering the Number

Visual Confirmation: You may see a small green triangle in the corner of the cell indicating a 'Number Stored as Text' warning. This means your formatting was successful.

Solution 2

Format Cells as Text Before Data Entry

Best for bulk data entry when you need an entire column or row to accept long numbers without altering them.

WPS Spreadsheet Solution

Handle Long Numeric Data Flawlessly in WPS Spreadsheet

WPS Spreadsheet allows you to easily format cells as text, completely preventing long sequences like credit cards and IDs from being truncated or rounded to zero.

1. Open WPS Spreadsheet: Launch WPS Office, open a new Spreadsheet, and select the cells you plan to use.
2. Format as Text: Right-click the selection, click 'Format Cells', navigate to the Number tab, select 'Text', and click OK.
3. Enter Your Data: Type or paste your 16-digit numbers confidently, knowing every single digit will be preserved perfectly.

Effortlessly batch-format columns to handle millions of long-number entries.100% format compatibility with Microsoft Excel files (.xlsx, .xls, .csv).Lightweight, lightning-fast performance for heavy data processing.Free alternative with a highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel change my 16th digit to a zero?

Excel conforms to the IEEE 754 specification for storing floating-point numbers, which strictly limits numeric precision to 15 significant digits. Any digit placed beyond the 15th position is automatically truncated and converted to zero to save memory.

Can I recover the exact 16-digit number if it has already been changed to zero?

Unfortunately, no. Once Excel changes the trailing digits to zero and the input is registered, the original data is permanently lost from that cell. You will need to re-enter or re-import the data after correctly formatting the destination cells as Text.

How do I import a CSV file with 16-digit numbers without losing the last digits?

Do not open the CSV by double-clicking it. Instead, open a blank Excel workbook, go to the Data tab, and select 'From Text/CSV'. In the import wizard, make sure to change the data type of your long-number column from 'General' to 'Text' before loading the data into your spreadsheet.

Can I perform mathematical calculations on numbers formatted as Text?

Usually, no. Mathematical functions like SUM or AVERAGE may ignore cells formatted as text. However, since 16-digit strings are almost exclusively used for identification purposes (like credit cards or serial numbers), mathematical operations are rarely required.

Excel & Data Problems