Keep long numbers and leading zeros intact when a CSV opens in Excel

What Excel does to a file of IDs and codes

A CSV file is plain text with no cell types, so when you double-click it, Excel guesses for each value whether it is a number, a date or text. The fictional file below holds the kinds of values that this guess gets wrong.

Both order references show as 4.02177E+19, but that is only the display. Underneath, Excel has kept just their first 15 digits, so both cells now hold the same value, 40217730091152400000. A wider column shows those zeros, not the original digits, and a lookup or duplicate check treats the two orders as one.

Scientific notation is only display; lost digits are real

4.02177E+19 is only how a General-format column that is too narrow displays a large number; a wider column or the Number format shows more. The real loss happens earlier: Excel stores numbers with 15 significant digits of precision, so every digit from the 16th onwards becomes zero during the conversion. A 16-digit card or account number already loses its last digit.

• Leading zeros disappear because 00412 and 412 are the same number. A custom format such as 00000 can show five digits again, but the cell still holds 412, and codes of varying length can't be restored that way.
• Long IDs lose every digit after the 15th. No format, formula or tool can recover them from a file that was saved after the change.
• Codes such as 3-12 or 12E3 become a date or a number in scientific notation, and the original text is no longer in the cell.
• Values that start with = are treated as formulas and replaced by their result.

Make an XLSX with text cells instead

Unlike a CSV, an .xlsx workbook stores a type for every cell, and Excel shows a text cell exactly as written. The CSV to Excel converter writes every value as a text cell by default. It runs in your browser and doesn't upload the file.

1. Open your CSV in the CSV to Excel converter, or select Try a sample to load the rows shown above.
2. Check the table under Input: IDs, zeros and codes appear exactly as in the file.
3. Leave Value types set to Keep every value as text.
4. Select Download XLSX and open the workbook in Excel.

In the workbook, 00412, all 20 digits of both order references, +15550100234, 3-12, 12E3 and =SUM(F2:F3) are text cells. A formula-like value is stored as plain text, so nothing is calculated when the file opens. Excel may flag number-like text with a small green triangle (Number Stored as Text); the value is unchanged.

When you need real numbers as well

Detect numbers and true/false makes a column numeric only when every value in it is a plain number that Excel can store unchanged. In the sample, quantity and unit_price become numbers, while customer_id (leading zeros), order_ref (more than 15 digits), part_code and note stay text.

Detection judges the form of the values, not their meaning, and decides for a whole column. A column made only of values like +15550100234 reads as signed whole numbers, and one made only of values like 12E3 as scientific notation, so in the sample phone and lot_code would become numbers too. For such files, keep the default and convert only the quantity cells in Excel: select them and choose Convert to Number from the warning icon.

If you have to start from the CSV in Excel

If a converted workbook isn't an option, don't double-click the CSV; import it and set the risky columns to Text before anything is loaded. In current Excel for Microsoft 365 that means Data > From Text/CSV > Transform Data, removing the automatic Changed Type step, setting the ID columns to Text and loading. The guide to importing a CSV file into Excel covers this and the older Text Import Wizard.

Recent Excel for Microsoft 365 and Excel 2024 builds also have automatic data conversion settings in the data options that can stop leading zeros, long numbers, E notation and date-like text from being converted. What exists and what it is called depends on version and platform, and the settings change only your own Excel, not a colleague's.

What doesn't protect the values

• Quotes around the value ("00412") only tell a CSV reader where a value starts and ends. Excel converts quoted numbers just the same.
• A UTF-8 byte order mark helps Excel read accented letters and other scripts correctly. It says nothing about cell types.
• Formatting the column as Text after opening comes too late, because the conversion has already happened.
• Formula wrappers such as ="00412" only work where a spreadsheet evaluates formulas. Other CSV readers, databases and scripts see the literal text ="00412", so the data in the file is no longer the ID.
• A leading apostrophe ('00412) is not a formula: typed into Excel, it marks the entry as text and isn't shown. In a CSV file, though, it is just a character, and other programs receive '00412 as the value.
• A real text cell in an XLSX keeps the plain value 00412 with no extra characters. That is what the converter writes, so ViewCSV adds neither wrappers nor apostrophes.

Limits

The converter handles files up to 25 MB, 100,000 rows, 250 columns and 2 million cells, one sheet per workbook. A file already saved with rounded IDs or stripped zeros can't be repaired by converting it; export it again from its source. ViewCSV's tests check the cell types in the generated XLSX with an independent reader; how a given Excel version formats them on screen can vary.