'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix Excel Losing Text Formatting When Saving as CSV
Question details
Users need a way to stop Excel from removing text formatting and altering large numbers into scientific notation when saving and reopening a CSV file.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Saving a spreadsheet that contains specifically formatted text, leading zeros, or long strings of numbers into the CSV file format and then reopening it.
- Observed behavior
- Cell formatting is entirely lost, and long numerical values are automatically converted and displayed in General format or scientific notation (e.g., 2.54E+11).
Before importing the data into a new workbook, ensure the CSV file is completely closed. If the CSV is currently open in Excel or another application, the import tool may fail to access the file.
Import CSV Using Power Query to Preserve Formatting
Use Excel's 'Get Data' feature to define the exact data type for each column during the import process, preventing automatic conversion to scientific notation.
Because CSV files do not store formatting data, double-clicking to open them forces Excel to guess the data types. Importing the file manually allows you to explicitly set problematic columns as Text.
Open a new, blank workbook in Excel. Go to the 'Data' tab on the ribbon, click on 'Get Data', select 'From File', and then choose 'From Text/CSV'.
Locate the CSV file you want to open in the file browser, select it, and click the 'Import' button.
A preview window will appear. Do not click Load immediately. Instead, click 'Transform Data' to open the Power Query Editor.
Select the column or columns that are losing formatting (like tracking numbers or IDs). Click the data type icon in the column header (usually '123' or 'ABC') and change it to 'Text'. Confirm by clicking 'Replace current' if prompted.
Once the formatting looks correct in the preview, click 'Close & Load' in the top-left corner to bring the correctly formatted data into your worksheet.
Verify Saved Values in a Plain Text Editor
Check the raw data in a text editor to confirm whether the formatting was actually lost during saving or if it is just a display issue when Excel reopens the file.
Save as an Excel Workbook (XLSX)
If you do not strictly need a CSV file for a third-party application, save your file in a native Excel format to inherently protect all formatting, colors, and data types.
Handle CSV Imports Easily with WPS Spreadsheet
WPS Office provides a highly compatible and user-friendly Spreadsheet application that includes a straightforward Text Import Wizard, allowing you to easily define text columns and prevent formatting loss for free.
- 1. Open the Data tab: Launch WPS Spreadsheet, open a blank workbook, and navigate to the 'Data' tab on the top ribbon.
- 2. Import your CSV file: Click 'Import Data', select 'Import Data' from the dropdown, and browse for your CSV file.
- 3. Follow the Text Import Wizard: Choose 'Delimited' and proceed. Select 'Comma' as your delimiter to correctly separate your columns.
- 4. Set column data format to Text: In the final step of the wizard, click on the column preview containing your long numbers and change the 'Column data format' from General to 'Text'. Click Finish to safely import your data.
Frequently Asked Questions
Why do my long numbers appear as scientific notation (like 2.54E+11) in a CSV?
CSV files are plain text documents that cannot save column formatting rules. When you open a CSV by double-clicking it, Excel defaults all cells to the 'General' format. This automatically triggers Excel to display any number exceeding 11 digits in scientific notation to save space on the screen.
Does a CSV file save cell colors, fonts, or borders?
No. CSV (Comma Separated Values) only stores the raw text and data separated by commas. It automatically strips away all visual elements, including cell colors, bold text, borders, and custom cell widths.
How can I permanently save my text formatting?
If you want to maintain specific text formatting, colors, and layout configurations, you must save your file using a native spreadsheet format, such as an Excel Workbook (.xlsx) or WPS Spreadsheet format, rather than as a CSV.
