How to Prevent Excel From Changing Numbers and Dates
Entering or importing a 16-digit card number or tracking ID turns it into "1.23457E+15" with the final digits destroyed, while product model numbers like "1-2" turn into "02-Jan".
Excel changes long numbers and dates because its calculation engine enforces two hardwired rules: 1) The IEEE 754 floating-point standard limits numerical precision to 15 significant digits. Any 16-digit number (like a credit card or barcode) is converted to scientific notation (e.g. 4.53211E+15) and all digits after the 15th are permanently replaced with zeros. 2) Excel's auto-detection heuristics aggressively convert any string matching common date formats (e.g. 1-2, 10/12, MAR-15) into serial date numbers. To prevent this, never open raw CSVs by double-clicking; import via Data → From Text/CSV and specify the column format as "Text", or convert via TextToTable where raw string values are preserved.
In This Guide
1. Why Does This Problem Happen? (Root Causes)
The 15-Digit Floating Point Wall: Excel complies with IEEE 754 double-precision floating point specification. It cannot store more than 15 digits of an integer as a number without rounding.
Auto-Scientific Notation Threshold: Any integer with 12 or more digits is displayed in scientific E-notation by default.
Irreversible Date Conversion: When Excel converts "1-2" to a date, it stores it as internal integer `45659` (days since Jan 1, 1900). Changing format back to text will display `45659`, not your original text.
2. How to Fix It (Step-by-Step Methods)
Step 1: Understand the 15-Digit Permanent Data Loss
If you paste a 16-digit credit card number `1234567890123456` into Excel under General format, Excel stores it as `1234567890123450`. The final `6` is permanently erased. You must store 16+ digit numbers strictly as text.
Step 2: Prepend an Apostrophe (') or Enclose in Quotes in TextToTable
In TextToTable, the parser handles numeric strings as text literals. When exported to XLSX, cells are flagged with string type (`t: "s"`), preventing Excel from applying numeric rounding or date coercion.
Step 3: Power Query Column Text Formatting
When importing via Excel's Data → From Text/CSV: Click "Transform Data". Look at the automatic "Changed Type" step in the Applied Steps list on the right. Delete the "Changed Type" step or explicitly click the header icon of your number/date column and choose "Text".
3. Verified Test Fixture & Output
Reproducible test input and the verified multi-column result.
CardNumber,PartNumber,RatioCode 4532112345678901,1-2,10/12 5412887654321098,3-4,15/20 3782822468101214,5-6,25/30
| CardNumber | PartNumber | RatioCode |
|---|---|---|
| 4532112345678901 | 1-2 | 10/12 |
| 5412887654321098 | 3-4 | 15/20 |
| 3782822468101214 | 5-6 | 25/30 |
4. Common Pitfalls to Avoid
5. Frequently Asked Questions
Can Excel increase precision beyond 15 digits?
No. The 15-digit limit is built into Excel's calculation core and cannot be changed. All 16+ digit numbers (credit cards, IMEI numbers, crypto addresses) must be stored as text strings.
How can I stop Excel from converting 1-2 to a date?
Type an apostrophe first: `'1-2`, or select the column, format as Text before pasting, or convert using TextToTable.
Related Guides & Tutorials
How to Preserve Leading Zeros When Importing CSV & Text into Excel
Keep leading zeroes in Excel when importing CSV, TXT, and copied data. Fix stripped zeros in ZIP codes, employee IDs, phone numbers, and SKUs.
Step-by-Step TutorialHow to Convert TXT to Excel Without Losing Data
Safely convert plain text and TXT files to Excel without losing leading zeros, converting IDs to scientific notation, or corrupting accented characters.
Authoritative Technical References & Standards
- • IETF RFC 4180 Specification: Common Format and MIME Type for Comma-Separated Values (CSV) Files.
- • Microsoft Excel Documentation: Import or export text (.txt or .csv) files in Microsoft Excel.
- • Unicode Consortium Standards: UTF-8, UTF-16, and Byte Order Mark (BOM) Specifications.