How do I retain leading zero zip code formatting when saving as a.

[Guest]

I'm using Excel 2003. I need to create an importable shipping address file
for our UPS shipping system so we can import all the information at once
without typing all 200 addresses by hand. The file to be imported must be
saved in a .csv (comma separated value) format. Many of the zip codes in this
file having leading zeros (New England areas). When I save the .xls file as a
..csv file, the .csv file does not show and/or retain the leading zeros. The
result is that the imported file has a bunch of 4 digit zip codes that get
rejected by UPS and have to be painstakingly hand-corrected.
I have tried virtually every cell format available that makes any sense (and
some no sense!). NOTHING seems to work.

HELP!

 

[Guest]

Hi,

Easiest thing to do is add a new column and use

=TEXT(A1,"0000000")

(being British I have no idea how many digits you actually need, but change
the latter parameter to the correct number). If you save this as a csv this
will work.

Regards,

David Jessop

 

[Guest]

Try puitting an apostrophe ' before you type the zip code. this should leave
the 0 as leading.

 

[Guest]

I tried it, but it doesn't work. Once the file is saved as a .csv file and I
open it to look at it, the 5 digit zip code with the zero in front of it has
gone back to a 4 digit code without the zero in the new column I added.

 

[Guest]

This was one of the first things I tried, but it doesn't work. The .csv
format doex not retain this information.

 

[Fred Holmes]

I suspect you can't fix it. CSV (Comma separated variables) in its
purest form is an ASCII file, a flat file database in which each line
is a record, and data fields are separated by the comma character. If
Excel natively stores the number without leading zeros, and the
leading zeros are provided for display only by formatting the cell,
then you will lose the formatting when you save the file as a CSV
file.

If you get yourself a good programmer's editor with a good macro
capability (The Semware Editor, perhaps), then writing a macro to
process the CSV file to run the zip code field and insert leading
zeros on any zip code that is less than five characters is a simple
process.

One thing you might try is to format the column (cells in the column)
with the zip code as "text" or even use the leading apostrophe, and
then see what happens when you save the file in CSV format. It should
save the file properly if it follows the logic of text cells and CSV.
But having done that, you might have to manually re-insert the leading
zeros in the Excel file. Try two copies of the worksheet. Copy from
the column in one sheet that is displaying zip codes properly. Format
the column in the destination sheet as text first and then
paste-special, values into that column. In principle that should
work.

Fred Holmes

 

[Guest]

I tried it, but it doesn't work. Once the file is saved as a .csv file and I
open it to look at it, the 5 digit zip code with the zero in front of it has
gone back to a 4 digit code without the zero in the new column I added.

Actually, David's approach works quite nicely on my Excel system. I
suspect your problem is that you've stored the new column out and tried
to read it back in and the format changed.

Instead, just store out the original number and when you read it back in
have the spreadsheet copy it to the new column with the TEXT statement.

Works great. When you load the file the column has the correct leading
zeros.

Bill

 

[Guest]

Hi,

I think the question is whether your UPS system is opening the file as text
or with Excel. If you open the CSV file with (say) notepad you will find that
the leading zeros are there. If you open it with Excel then I agree, Excel
will (helpfully) re-strip the leading zeros.

Can't think of much more than this.

David Jessop