CSV Number Format problem

[Mervyn Thomas]

have some confusion with number formats in CSV files. Using a custom
number format 00000 I get numbers such as
00392 to display OK when on the screen when viewed in Excel but appears as
392 when the file is
repopened after saving.
The formats do appear OK in the saved file when opened with notepad.
However when the CSV file is
reopened in excel the number format is lost and appears as 392 rather than
00392. This also happens when the number is input as '00392 rather than
just using the custom format..

Can you explain what is happening here and what do you think external
systems will do with these numbers? Is there any way to maintain the 00000
formats?

Mervyn

 

[Dennis]

Seems that maybe there are two issues:

First, .csv files when saved do not retain any on screen formatting. In
fact the user is warned about that when saving files as .csv.

Second, if you are in a true .xls (Saved As .xls) file, add, if
necessary, another column next to the one that has the numbers. Format
the new column as text. Copy>Paste Special>Values (only) to the new
column. The leading zeros should display/print properly. Just realize
the data is now text and cannot directly be added etc.

Also, consider using Format>Cell>Numbers>Custom with a "Mask" that suits
your needs. The numbers will retain their number property AND that will
display/print as you seem to want

Dennis

 

[ChrisJForeman]

If this is a reference number, not one that you wish to do arithmetic on, then
format the cell(s) as 'general' (Format->Cells->General).

HTH

Chris