Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Saving File as CSV-zeroes removed

    I have a file the has zeroes after the decimal place. When saving as a CSV file the zeroes fall away. Is there any way to prevent this from happening?
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    CSV files should be saved as formatted but if you reopen the csv file in Excel, it will default to a general format and not display decimal places for integer values. This does not mean that the CSV file is stored that way - you can verify by opening in Notepad.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Howard

    If you look carefully at your column [B] values, you will see that the actual values stored by Excel (as per the value shown in the formula bar) are whole numbers. It's only your display format that shows the 2 decimals .00
    Ditto, if you put your cellpointer in cell [C4], and then highlight the formula in the formula bar (use the mouse to drag over the entire formula in the formula bar), then press [F9] key to calc the highlighted formula, you will the result is also a whole number.

    So, the number format only specifies how data is displayed and printed, it does not affect the actual value stored by Excel: e.g. 12.5%, 12.50% and 12.500% are all stored exactly the same way.

    A .csv file is a plain text file; it contains the displayed values, but not formatting information. If you save a worksheet as a .csv file then open the .csv file again, Excel will apply the default formatting.
    You can apply custom formatting, but that will be lost again if you save the .csv file.

    If you open your csv file with notepad (or any text editor) you will see that it does contain the decimal zeros. So these are being preserved in the csv file, it's just that when you open the csv file back again in Excel, you need to reapply any formatting you want

    zeddy
    Last edited by zeddy; 2013-07-30 at 11:05. Reason: typos

  4. #4
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Guys

    Thaks for the help. is there any way that if I enter say 1.00, 175.00 that Excel display this as 1.00, 175.00 without having to format this?

    Regards

    Howard

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    No, because that is a formatting issue. You could alter the Normal style to use two decimal places by default.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Rory

    How do I go about doing this?

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Howard

    ..perhaps you could try setting up (or modifying an existing) book.xlt file in your XLSTART folder


    zeddy

  8. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the info, much appreciated

    Regards

    Howard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •