Exporting to Excel - Losing the first zero of zip code

[melma]

I am exporting customer information from a MSSQL to an .xls using ASP. The exporting goes very well, all the info gets out ok except for the zip codes starting with zero (0), the data exports right (all five digits) but Excel represents the zip code as a "number" and it cuts off the first zero leaving the zip code with 4 numbers. I can always go to "format cell" , "special" , "zip code" and that will fix the problem but I am wondering if there's a way around this....

Mel

[sashi]

Hi Mel,

how is your zip code stored in your SQL database? what is the datatype used?

[melma]

varchar 50 nullable

[Banfa]

This is because EXCEL is treating the zip code as a number not as a string of digits. You can force EXCEL to treat it as a string by prepending a '

Data in SQL    Data in Excel
    76235          '76235
    93675          '93675
    07352          '07352
 

[melma]

That works but I would like to have a clean entry without the '

[iam_clint]

melma export it to something else then set your fields in excel to be string and not numeric because numericly the 0 is nothing so it removes it if you set the format to be string it should work fine when you move the data to it, i have had the same problem awhile back but i used the solution banfa just gave the ' before the number.
theres also a zipcode format in excel i am pretty sure that format would keep the preceeding 0's

if you read this banfa i think we need an excel topic somewhere.

[softomic]

That works but I would like to have a clean entry without the '

Why wouldn't a single quote make it a clean entry since you are entering text and not numbers. One thing you could try is to format the cells before you insert the zipcodes. If you select the column and click on Format > Cells > Number > Text, then the cell will be ready to accept numbers as text and shouldn't do anything to the input. Or you can even use the Format > Cells > Number > Special > Zip Code format.

One other possibility is to use a formula like =TEXT(B1,"000000"), so the number appears in the 5-digit zip format.

This is nice and all, but won't work for zip codes other than those in the US.