Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post
    I have an Excel file with a column of whole numbers. I need them to have 2 decimals like 275.00, no dollar sign. I formatted the column with currency no $. But when I convert the file to a csv, it goes back to 275. Is there a way i can keep the 275.00? Thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Is it the SAVE As that is the issue or reopening the file?

    I am unable to reproduce the Save As problem. When I save as a CSV, the file saves the text the way that it is displayed.

    I do lose it when the CSV is re-opened in XL, but this expected as it is an XL formatting issue where it is displayed as general unless there is some indication in the number that it should be a special format.

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Forget this reply - see the next one by Steve.

    [s]The number format merely tells Excel how to display the value. When you export to csv, the internally stored value 275 is exported, not the displayed version.
    If you want 275.00 in the exported file, you have two options:

    1. Store the values as text, either by formatting the column as text and entering 275.00, or by prefixing the value with an apostrophe: '275.00.

    - or -

    2. Keep the values as numbers, and create a column with formulas next to it, like this:

    =TEXT(A1,"0.00")

    or

    =TEXT(A1,"#,##0.00")

    if you want thousands separators.[/s]

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    [quote name='HansV' post='800177' date='27-Oct-2009 16:00']The number format merely tells Excel how to display the value. When you export to csv, the internally stored value 275 is exported, not the displayed version.[/quote]

    Is this different in different versions? When I checked in XL2002 what was put into the CSV was the formatted text (with the decimal points and even with the dollar sign in currency mode). When re-imported excel removed those decimal digits just like if one had entered them manually (except excel dutifully formatted the text to currency with 2 decimals for the importing of a number that was clearly currency)...

    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'm getting rusty - I should start checking my replies! You're completely correct - the .csv file contains formatted values but Excel ignores the formatting when opening the .csv file.

Posting Permissions

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