Problems with leading zeroes when converting to a .csv file

Here is an example of how you can restore the missing leading zeros.

If your original number is 12 characters long and you lost one leading zero use TEXT function with 12 zeros format

=TEXT(A1,"000000000000")

Row\Col	A	B
1	39897733890	039897733890

Originally Posted by tensetoast

When I convert my excel file to a .csv file I lose the leading zeroes in the SKU column. Example 00325 converts to 325. My UPC column converts to a scientific number. Example 039897733890 becomes 3.99+10.

Originally Posted by AlKey

Here is an example of how you can restore the missing leading zeros. If your original number is 12 characters long and you lost one leading zero use TEXT function with 12 zeros format
=TEXT(A1,"000000000000")

You misunderstand the problem. And writing the data as text does not solve it.

If the cells display leading zeros in Excel, they are written to the CSV file with leading zeros, regardless of whether the Excel values are text or numeric.

You can verify that by opening the CSV file in Notepad or the equivalent.

The problem arises when you open the file directly in Excel. Excel tries to interpret the data as numeric. It does not matter what type the data was when it was written to the CSV file. Remember: a CSV file is just plain ASCII (text); there is no format or type information.

(And by the way, it does not help to surround data with double-quotes. Excel still interprets the data the same as if there were no double-quotes.)

We see 3.99+10 for 039897733890 because the cell is formatted as General, and such large numbers are displayed in Scientific form (although the cell format remains General).

And by the way, that bodes ill for CSV data that appear to be numbers with more than 15 significant digits. In that case, Excel truncates the data after 15 significant digits, filling in with zeros. For example, 001234567890123456 will be interpreted as the number 1234567890123450, displayed as 1.23457E+15.

One possible remedy is to import the CSV (Get External Data / From Text) instead of opening it directly. The Import Text File wizard gives us the opportunity to treat entire columns as text instead of numeric data.

Of course, that is useful only if all of a column can be treated as text.

In the case of columns with mixed types of data, the only remedy is write a VBA routine to import the data, relying on the VBA algorithm to treat some input as text.