How to Prevent Scientific Notation When Saving Excel Numbers to CSV
Question details
The user needs to stop large numerical values from automatically converting to scientific notation when exporting spreadsheet data to a CSV file.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Exporting large numerical data such as IDs, barcodes, or serial numbers to a plain text CSV format.
- Observed behavior
- Large values (e.g., 910049521863) display as scientific notation (e.g., 9.1005E+11) when the CSV is saved and reopened, because CSV plain text format strips Excel's custom number formatting.
Before exporting to CSV, ensure your original spreadsheet contains the exact full numbers you wish to preserve. Always keep a backup of your original .xlsx workbook in case you need to re-export the data.
Convert Numbers to Text Using a Formula Before Exporting
Converting your large numbers to a text string guarantees that the CSV format will preserve every digit without triggering scientific notation.
Since CSV is a plain text format, it does not retain cell formatting rules. By converting the numbers into actual text strings via a formula, you lock in the visible digits before saving.
Insert a new column next to your data. Enter the formula =TEXT(A1, "0") (assuming A1 is the cell with your long number) and press Enter.
Click and drag the fill handle at the bottom right of the formula cell down to apply this conversion to all numbers in the column.
Select the newly created text column, copy it, and then right-click the original number column to select 'Paste as Values'. This removes the formula and leaves only the text numbers.
Go to File > Save As, choose your destination, and select 'CSV (Comma delimited) (*.csv)' as the file format. Click Save.
Import CSV Columns as Text When Reopening
If you need to reopen the saved CSV file in a spreadsheet, you must import the data rather than opening it directly to stop it from reverting to scientific notation.
Easily Export and Import CSV Files without Data Loss in WPS Spreadsheet
WPS Spreadsheet provides an intuitive Text Import Wizard that makes it incredibly simple to assign proper column formats like Text. This ensures your large numbers, barcodes, or IDs never get automatically converted into scientific notation.
- 1. Create a blank spreadsheet: Open WPS Spreadsheet and start a new blank document.
- 2. Import your CSV data: Go to the Data tab and click 'Import Data', then select 'Import Data' again to browse for your CSV file.
- 3. Choose delimited format: In the Text Import Wizard, choose 'Delimited' and click Next until you reach the step allowing column formatting.
- 4. Format as Text: Click the header of the column containing long numbers, choose 'Text' under Column data format, and click Finish.
Frequently Asked Questions
Why do spreadsheets automatically change long numbers to scientific notation?
Spreadsheet programs generally format numeric values exceeding 11 digits into scientific notation (e.g., E+11) automatically to display them compactly within a standard cell width.
Can I format a CSV file directly to prevent scientific notation?
No. A CSV (Comma Separated Values) is a plain text format that cannot save custom visual formatting, cell colors, or column widths. The numbers must be handled as text strings before exporting, or imported explicitly as text when reopening.
Does adding an apostrophe before the number work?
Yes. Typing a single apostrophe (') directly before a number forces the spreadsheet software to treat that entry as a text string, bypassing any automatic numeric formatting or scientific notation.
