Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2014
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Converting to CSV Truncates Decimals

    I'm attempting to covert a excel file to a CSV and I cannot get the numbers/format I need.

    I have tried converting the decimals to text

    =TEXT(A1,"#.00000")*100

    3.625% will come over as 3.625 (missing the %)

    3.500% will come over as 3.5 (missing 00 and %)


    How can I take the formatted data and have the same values returned in the same format in a csv?

    Thank you in advance

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Gargo,

    Try,
    Code:
    =text(A1,"#.0000%)
    
    Or
    
    =Text(A1,#.0000) & "%"
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Delaware, US
    Posts
    1,172
    Thanks
    19
    Thanked 99 Times in 88 Posts
    Quote Originally Posted by Gargo View Post
    I'm attempting to covert a excel file to a CSV and I cannot get the numbers/format I need.
    A CSV file is nothing more than a delimited text file, you can actually open it with Notepad and look at it. It contains data that can be used to transfer data between different programs.

    When you format data in Excel, all you are doing is putting a "mask" on the cell(s) that tells it how to display. That mask isn't a part of the data and isn't saved when you export the data - which is what you are doing when you save it as a CSV. IOW, creating a CSV exports the raw data, not the formatting.

    When you or anyone else opens the CSV, Excel will read the data there and format it according to it's defaults (which you cannot change). Since the % is merely a formatting option, it isn't saved and won't be carried on to any other program.

    RetiredGeek has given you a way to turn the number into text and have it save in a CSV as text. That's a good solution if you are sure that the person receiving the file is OK with it. But you have to be careful about who gets the file and what they do with it since you are basically changing the nature of the data - you are converting a number into text. It's no different than changing 22 into twenty-two.

    Frankly, my feeling is that a CSV file should be just what it's supposed to be, a data file containing raw data. If you want to give someone formatted data, then give them an Excel sheet.
    Graham Smith
    DataSmith, Delaware
    "For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,207
    Thanks
    49
    Thanked 989 Times in 919 Posts
    The raw data "behind" 3.625% should be "0.03625". Change the formatting in Excel to show the data unformatted to confirm.

    cheers, Paul

Posting Permissions

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