Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I retain leading zero zip code formatting when saving as a.
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! |
#2
|
|||
|
|||
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 "RodFCIS" wrote: 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! |
#3
|
|||
|
|||
Try puitting an apostrophe ' before you type the zip code. this should leave
the 0 as leading. "RodFCIS" wrote: 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! |
#4
|
|||
|
|||
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. "David Jessop" wrote: 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 "RodFCIS" wrote: 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! |
#5
|
|||
|
|||
This was one of the first things I tried, but it doesn't work. The .csv
format doex not retain this information. "gennario" wrote: Try puitting an apostrophe ' before you type the zip code. this should leave the 0 as leading. "RodFCIS" wrote: 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! |
#6
|
|||
|
|||
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 On Tue, 25 Jan 2005 10:51:04 -0800, "RodFCIS" wrote: This was one of the first things I tried, but it doesn't work. The .csv format doex not retain this information. "gennario" wrote: Try puitting an apostrophe ' before you type the zip code. this should leave the 0 as leading. "RodFCIS" wrote: 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! |
#7
|
|||
|
|||
RodFCIS wrote:
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 |
#8
|
|||
|
|||
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 "RodFCIS" wrote: 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. "David Jessop" wrote: 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 "RodFCIS" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lost my Paste | Excel Discussion (Misc queries) | |||
how do you display a zip code with a leading zero | Excel Discussion (Misc queries) | |||
Losing conditional formatting when saving | Excel Discussion (Misc queries) | |||
Formatting dates in the future | Excel Worksheet Functions | |||
Copy conditional formatting across multiple rows? | Excel Discussion (Misc queries) |