How to Paste a 20-Digit Number in Excel Without Losing Digits
Question details
The user needs to paste 20-digit numbers into Excel without the digits after the 15th position converting to zeros.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Pasting long identifiers, such as 20-digit credit card numbers, tracking codes, or IDs, into an Excel spreadsheet.
- Observed behavior
- Excel preserves only the first 15 significant digits of standard numeric values, automatically replacing any subsequent digits with zeros.
Determine whether the long numbers you are pasting require mathematical calculations. Formatting them as text prevents data loss but disables standard math functions for those specific cells.
Format the Target Cells as Text Before Pasting
Pre-formatting the destination cells as text forces Excel to treat the pasted number as a string, preserving all 20 digits accurately.
This is the most reliable method when pasting a large batch of long numbers. It must be done before the data is pasted, otherwise Excel will have already truncated the numbers.
Highlight the cell, column, or range where you intend to paste the 20-digit numbers.
Right-click the selected area and choose 'Format Cells', or press Ctrl+1 (Command+1 on Mac).
Navigate to the 'Number' tab, select 'Text' from the category list, and click 'OK'.
Copy your 20-digit numbers and paste them into the newly formatted text cells.
Prefix the Number with an Apostrophe
A quick, manual workaround for entering or pasting a single 20-digit number without changing the cell's underlying format.
Import Data Using Power Query
Best suited for importing large external datasets (like CSV files) containing 20-digit numbers, allowing you to set the data type during the import process.
Preserve Long Numbers Easily with WPS Spreadsheet
WPS Office provides a lightweight, highly compatible spreadsheet tool that seamlessly handles large datasets and long numeric strings. You can easily pre-format cells to safeguard your 20-digit numbers from unwanted truncation.
- 1. Open your workbook: Launch WPS Spreadsheet and open or create a new workbook.
- 2. Select and format cells: Highlight the target cells, right-click, select 'Format Cells', and choose 'Text' under the Number tab.
- 3. Paste your data: Paste your 20-digit numbers into the cells. WPS will preserve all digits perfectly without converting them to zeros.
Frequently Asked Questions
Why does Excel change my 20-digit numbers to zeros after the 15th digit?
Excel follows the IEEE 754 specification for floating-point calculations, which restricts numeric precision to a maximum of 15 significant digits. Any digits typed or pasted past the 15th place are automatically truncated and converted to zeros.
Can I recover the digits if they have already turned into zeros?
No. Once the numbers are pasted into a standard numeric cell and converted to zeros, the original trailing digits are permanently lost in that workbook. You must format the cells as text first, then paste the original data again from your source.
Is there a way to do math with a 20-digit number in Excel?
Standard Excel functions cannot calculate beyond 15 digits of precision. To perform arithmetic on 20-digit numbers, you would need to use specialized Excel add-ins that handle arbitrary-precision arithmetic, or process the data using a programming language like Python or VBA.
Does this 15-digit limit apply to WPS Spreadsheet as well?
Yes. WPS Spreadsheet, like almost all major spreadsheet applications, adheres to the same universal 15-digit precision standard. You will need to use the exact same text-formatting methods in WPS to preserve 20-digit strings.
