How to Convert and Preserve 23-Digit Numbers in Excel
Question details
The user needs to prevent Excel from converting 23-digit identifiers into scientific notation and truncating the remaining digits to zeros.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Preparing and importing hundreds of rows containing 23-digit identifiers from a CSV file into an accounting system.
- Observed behavior
- Excel automatically converts long numeric values to scientific notation and limits precision to 15 significant digits, changing any trailing digits to zeros.
Before opening your data file, ensure you have the original CSV or text file saved locally on your computer. Do not open the export directly from your web browser, as this immediately triggers Excel's automatic number formatting.
Import the CSV File and Set Column Format to Text
The most reliable way to prevent Excel from altering long numbers is to import the data and explicitly define the column format as Text before the data loads into the sheet.
By using the Text Import Wizard instead of double-clicking the CSV file, you force Excel to treat the 23-digit identifiers as text strings, which completely bypasses the 15-digit precision limit.
Open a blank workbook in Excel. Go to the 'Data' tab on the ribbon, select 'Get External Data', and click on 'From Text'. Locate and select your saved CSV file.
In step 1 of the wizard, choose 'Delimited' and click Next. In step 2, select the appropriate delimiter for your file (such as Comma) and proceed to step 3.
In the Data Preview window, click on the column containing your 23-digit numbers so it is highlighted. Under the 'Column data format' section, select 'Text', then click 'Finish' to import your data.
Use the TEXT Formula to Convert Existing Data
If you are copying data from another source and it displays in scientific notation, you can use a helper column with a specific formula to format the full 23 digits.
Easily Import and Manage Long Numbers with WPS Spreadsheet
WPS Spreadsheet offers an intuitive text import wizard that helps you handle long identifiers without losing precision. Seamlessly manage large datasets for your accounting systems with zero format loss.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a new blank spreadsheet.
- 2. Import Your Data: Navigate to the 'Data' tab, click on 'Import Data', and select your CSV or text file.
- 3. Format as Text: Follow the import prompts and ensure you select 'Text' for the column containing your 23-digit identifiers before completing the import.
Frequently Asked Questions
Why does Excel change my 23-digit numbers into zeros?
Excel has a strict mathematical precision limit of 15 significant digits. Any digits beyond the 15th position in a number are automatically converted to zeros to save memory. Formatting the cell as Text prevents this from happening.
Can I permanently stop Excel from converting long numbers to scientific notation?
While you cannot completely disable Excel's automatic scientific notation for long numerical entries, you can bypass it entirely by formatting the target cells as Text before typing or pasting your data, or by using the Data Import wizard for CSV files.
How do I fix long numbers that have already been converted to zeros?
Unfortunately, once Excel converts digits past the 15th position to zeros and the file is saved or the clipboard is cleared, the original data is permanently lost. You will need to re-import or re-paste the data from the original source using the Text format method.
