Exporting ZIP code to CSV drops leading zero

[Byron]

When exporting a spreadsheet to CSV format, I am loosing
the leading zero. I have formatted the column to ZIP code
format and the zero is displayed in the spreadsheet.

It is only when I export that I loose the leading zero. Is
there a patch or workaround for this problem?

 

[Dan E]

Byron,

Here's a macro to export all cells as text (ie the numbers come
out as displayed in the cells). However, if you open the CSV
in excel afterwards it will lose formatting and the leading zeros
will again disappear.

Sub ExportCSV()
Dim fs, f
OutFile = Application.GetSaveAsFilename(FileFilter:="CSV (Comma delimited)(*.csv), *.csv")
If OutFile = False Then Exit Sub
ActiveSheet.UsedRange.Select
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile(OutFile, 2, -2)
For Rcount = 1 To Selection.Rows.Count
For CCount = 1 To Selection.Columns.Count
Outline = CStr(Selection.Cells(Rcount, CCount).Text)
f.Write Outline
If CCount <> Selection.Columns.Count Then f.Write ", "

Next
f.Write vbCrLf
Next
f.Close
Set f = Nothing
Set fs = Nothing
End Sub

watch out for line wrapping in the posted code.

If you haven't used macro's before, David McRitchie's website
may be of some help to you:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Dan E

 

[David McRitchie]

Hi Byron, and Dan E.,
If you save the file (or rename the file) as .txt, you will be placed into the
Text Import Wizard when you open the file and you will have a choice of
making the column text or number.

In fact you don't need the macro you can save the file as text
Save As, Tab Delimited Text (.txt)
would be fine.

To open the file:
File, Open, Tab, Delimited
for the appropriate column change general to Text.

You could record a macro when you do this.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm