CSV to Excel formatting fixes for zeros and dates

A CSV opened in Excel can lose the leading zero of a phone number, turn a code such as 12/2 into 2-Dec, or show an ID as 1.23457E+12. Excel guesses a type for every cell the moment the file opens, and a CSV gives it nothing to go on. Stopping that guess before the file opens keeps every value exactly as it is in the source file.

Why Excel changes CSV values when you open the file

A CSV is plain text, so Excel infers a type for every cell, and that single guess causes every formatting break. The file holds values separated by commas and no information about what kind of data each cell contains. Excel reads a digit string as a number, a value shaped like a date as a date, and a long digit string as a number shown in scientific notation.

A leading zero is the 0 at the front of a code such as 0312345678, and a number has no use for it. Scientific notation is the short display, such as 1.23457E+12, that stands in for a long number. Both come from the same guess:

The three patterns need three different cleanups after the fact, but only one fix before it: take the guess away from Excel. An order export, a customer list and a product master all break the same way, because the cause sits in the format and not in the system that wrote the file.

How to open a CSV in Excel without losing leading zeros

Three routes stop the guess before it changes anything, and double-clicking the file is not one of them. A double-click opens the CSV with the default auto-conversion already applied, so the zero is gone before any later step can help. The three routes are a Microsoft 365 setting, the Data tab import and a conversion to xlsx.

The older Text Import Wizard also lets you set a type for each column, and it suits Excel versions without Power Query. The three routes below cover current versions.

Turn off automatic data conversion in Microsoft 365

With the Automatic Data Conversions setting off, Excel no longer removes leading zeros or converts dates when it opens a CSV. The setting is included in Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024 and Excel 2024 for Mac. A person who opens order exports or customer lists every week can switch the conversion off in the settings instead of fixing each file.

Versions that do not list the setting are better served by the Data tab import or by converting the file to xlsx first. Both work in any version that offers them and need no change to Excel itself.

Import a CSV into Excel with the Data tab and Power Query

Import the file instead of opening it, and set the column type to Text before the data loads. The Data tab import, the route most Excel import CSV guides recommend, gives control over each column, which a double-click never does. The steps:

1. Open a blank workbook in Excel.
2. Go to the Data tab and choose From Text/CSV.
3. Select the CSV file.
4. Choose Transform Data to open Power Query.
5. Set the type of every code, ID or leading-zero column to Text, then load the data.

A column set to Text before loading is never read as a number, so the zeros survive. The same step keeps date-shaped values such as 12/2 as they are in the file. Power Query is the editor that opens at step 4, and it is the only place where the type is set before Excel decides.

Convert the CSV to xlsx before you open it

Converting the CSV to xlsx first stores each cell's type explicitly, so Excel has nothing to guess when the file opens. Reopening the xlsx file later does not trigger the guess again, which makes the conversion the safest route for a file that is passed on to a colleague. A recipient who double-clicks an xlsx file needs no import steps.

The sakuttoAI CSV to Excel Converter turns CSV files into xlsx files in the browser, handles several files in one run and never sends the files to a server. The steps:

1. Drag and drop the CSV file onto the page.
2. Check in the preview that leading zeros and date-shaped values are unchanged.
3. Click Convert to download the xlsx file.

To convert a CSV to xlsx for sharing or archiving, this route takes less effort than the Data tab import, because there is no column to set. For the full conversion workflow, CSV to Excel: convert without losing leading zeros covers the broader question.

Keep CSV dates and long numbers from changing in Excel

Hyphens and slashes in a value trigger date conversion, and a long digit string is shown in scientific notation, so both need the type set before Excel reads the file. The value 12/2 becomes 2-Dec, and 1234567890123 becomes 1.23457E+12. Both are the same guess applied to different values.

Each route stops both patterns. With the Automatic Data Conversions setting off, Excel stops converting dates. With the Data tab import and the column set to Text, the value is never read as a date or a number, so there is nothing to convert and nothing to display in scientific notation. A converted xlsx file keeps the cell type from the start.

Product codes, serial numbers and tracking numbers are the usual long values. Any of them can be kept as it is in the CSV by choosing one route before the first open.

What to do if you already saved the damaged file

Export the data again from the source system: changing the cell format after the damage does not bring the original values back. The mangled values are written back into the file when it is saved, so the original zeros and codes exist only in the system that produced the export.

If the file is not saved yet, close it without saving and open it again with one of the three routes. If it is saved, download a fresh export from the source system and open that copy by the Data tab import, with the setting off, or after converting to xlsx.

Stop Excel from turning typed values into dates

Typed and pasted values are a separate case: format the cells as Text before entering them, because Microsoft states there is no way to turn the conversion off. Typing 12/2 into a General cell shows 2-Dec, and the three routes for opening a file do not apply to values entered by hand.

Microsoft's guidance is to preformat the cells that will receive the numbers as Text. Microsoft puts the limit plainly: "Unfortunately there is no way to turn this off. But there are ways to get around it." For typed values, Text formatting before entry is the documented workaround.

Choose the fix by how you open the file

Pick the route by what is being done with the file, and stop the guess before Excel changes the data. Each situation has one route:

Every row sets the type before Excel can guess one. Garbled characters and split columns belong to a different problem, which UTF-8 vs Shift-JIS: Why CSV Files Get Garbled covers. To read a CSV without editing it, View CSV Files Online, or in Excel and Sheets lists the options.

Stopping the guess works better than repairing cells afterwards, because every break above is that one guess landing wrong. Turn off Automatic Data Conversions in current Excel versions, import through the Data tab with the column set to Text, or convert to xlsx before opening, and keep a fresh export from the source for any file that is already saved damaged.