why does an Excel column formatted as number lose its leading zero when the column is formatted as text?

I have to export a spreadsheet to CSV. One of the columns is a zipcode with east coast zipcodes that have a leading zero. When I reformat that column as Text hoping to preserve the leading zero on export, immediately the column goes from right justified to left justified and the leading zero goes away! I would expect just the opposite, that it might lose the zero if reformatted from text to number.

How do I preserve that leading zero?

Solution 1:

Theory

Excel has two different, unrelated notions when it comes to data in cells: raw/stored value, and display value.

The "raw" or stored value is the actual data in an unformatted representation. The type of the raw data can be number, text, date, etc.

The "display" value is how the data is formatted. When using the Excel GUI, you see the display value, not the raw value.

The problem is, when you export to CSV, it is taking the raw value and exporting it, not the display value! So, even if you format the data to contain leading zeroes using the Format Cells dialog, if the underlying value doesn't contain a leading zero, then your export won't either.

You need to format all the data as text (in the Excel spreadsheet), and then add a leading zero when appropriate. This will eliminate the display value faking you out into thinking that you have a leading zero in the raw value (you don't).

Solution

The zero doesn't exist as part of the cell contents, just the formatting, so when you format as text you don't get it. You could try using a formula in the next column, e.g. if you have 5 digit (?) zip codes with entries in A2 down put this formula in B2

=TEXT(A2,"00000")

copy the formula down the column

The result of that is a text value (possibly with actual leading zero). Once you have this value in another column, you can copy and do "Paste Values" over the original column to replace all the underlying data with the zero-padded data. Then delete the extra column.

Solution 2:

You have numbers in the cells, not text. A numeric value of 02010 is the same as the number 2010. Formatting that as text gives the normal text representation of 2010, not 02010.

To preserve leading zeros, enter the data as a string, not a number. For example, enter ="02010" as the value.

To simply display leading zeros, assuming all numbers are the same length (say, five digits), use a Custom format. Select 0 from the Custom FOrlist, then change it to 00000. All numbers will show as five digits, with leading zeros. However, if you save this data to a CSV file, the leading zeros will not be there. The only way to keep them is to enter the data as strings.