Data Import & Export

Set Number Formatting When Opening a CSV File in Excel

Kushani NimanthikaKushani Nimanthika Nov 12, 2026 868 views

Question details

The user needs imported CSV values to display with required number formats, such as dates, decimals, currency, or leading zeros.

How to Set Number Formatting When Opening a CSV File in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Create a formatted template

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.

2
Save the workbook as a template

Select File > Save As, choose Excel Template (*.xltx) if available, and save the file for reuse.

3
Import the CSV data

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.

4
Keep the formatted workbook

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.

Import the CSV into a workbook template with preset number formats
CSV format limitation: If you save the finished sheet back to CSV, the number formats will be removed because CSV stores values and separators only.
Format CSV Data in WPS Office

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. 1. Open the CSV file: In WPS Spreadsheets, select Menu > Open and choose the CSV file, then confirm the delimiter options if prompted.
  2. 2. Select the target column: Click the column letter for the imported values that need formatting.
  3. 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. 4. Save as an XLSX workbook: Select Menu > Save As and choose Excel Workbook (*.xlsx) so the applied formatting is retained.
Apply Text formatting to preserve leading zeros in codes and identifiers.Set number, currency, decimal, and date formats for imported CSV columns.Open and save Microsoft Excel XLSX and XLS files with strong format compatibility.Use a familiar spreadsheet interface for quick CSV import and editing.
microsoft office alternative - wps office

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.