Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

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.

share|improve this answer
    
Hi Barry, excellent answer, I added a bit of background "theory" rather than posting yet another answer... feel free to edit as desired, this is a collaborative answer :-) –  allquixotic Dec 20 '12 at 16:15
    
@allquixotic, no problem, nice additions –  barry houdini Dec 20 '12 at 16:21

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.

share|improve this answer
    
We're getting this data from a variety of third parties. Looks like we're out of luck, as we're at the mercy of the people entering the data and cannot force them to do it in an intelligent manner. I was surprised that converting it to text stripped the zero. That IMO is a side-effect, not a mere string representation of the displayed value, whereas I would expect a cell formatted as number to strip a leading zero. –  mrblint Dec 20 '12 at 19:27

It has been a long time since I used Excel, but I always used to prefix the leading zero with a ' and Excel would treat it as a string instead of trying to interpret it as a number.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.