Set Number Formatting When Opening a CSV File in Excel
Question details
The user needs imported CSV values to display with required number formats, such as dates, decimals, currency, or leading zeros.

- Product
- Microsoft Excel CSV files
- Device & OS
- not provided
- Scenario
- A CSV file is opened or imported into a spreadsheet and requires consistent number formatting.
- Observed behavior
- CSV files store plain text and values only, so Excel number formatting is not retained when the file is reopened.
Decide which columns need formats before importing, such as Text for ID codes and ZIP codes, Date for date values, or Number/Currency for amounts. Keep the source CSV delimiter and column order consistent if you plan to reuse a template.
Import the CSV into a workbook template with preset number formats
Use this method when you regularly receive CSV files with the same column layout and want the formatting to be ready in advance.
A CSV format cannot save spreadsheet formatting. A separate Excel workbook template can store the required column formats, formulas, and layout while the CSV supplies the current data.
Open a blank workbook in Excel, select each target column, and use Home > Number Format to choose Text, Number, Currency, Date, or Custom formatting as needed.
Select File > Save As, choose Excel Template (*.xltx) if available, and save the file for reuse.
Open the template, go to Data > From Text/CSV, select the CSV file, review the delimiter and data preview, and choose Load to place the data into the worksheet.
After confirming the imported values display correctly, use File > Save As and save the working file as an Excel Workbook (*.xlsx), not as CSV, to preserve the applied formats.

Apply number formats after opening the CSV directly
Use this method for one-time or occasional CSV files when creating a reusable template is unnecessary.
Import CSV Data and Apply Number Formats in WPS Spreadsheets
WPS Spreadsheets lets you open or import CSV data, set column-specific number formats, and save the result as an XLSX workbook so the formatting remains available after closing the file.
- 1. Open the CSV file: In WPS Spreadsheets, select Menu > Open and choose the CSV file, then confirm the delimiter options if prompted.
- 2. Select the target column: Click the column letter for the imported values that need formatting.
- 3. Set a number format: Go to Home > Number Format and choose Number, Currency, Date, Text, or More Number Formats for a custom format.
- 4. Save as an XLSX workbook: Select Menu > Save As and choose Excel Workbook (*.xlsx) so the applied formatting is retained.

Frequently Asked Questions
Why does Excel remove formatting when I reopen a CSV file?
CSV is a plain-text format that stores values separated by commas or another delimiter. It does not store cell styles, column widths, formulas, or number formats.
How can I keep leading zeros when importing a CSV file?
Import the CSV through Data > From Text/CSV and set the relevant field or destination column to Text. If the file is already open, select the column and choose Home > Number Format > Text before entering values again.
Can I save a CSV file with currency and date formatting?
No. You can apply currency and date formats while viewing the data, but saving as CSV removes those formats. Save a separate copy as an XLSX workbook to retain them.
What is the best way to format recurring CSV reports?
Create an Excel template with the required formats in the expected columns, then import each new CSV into that template. Save each completed report as XLSX if the formatting must remain.




