+ Reply to Thread
Results 1 to 5 of 5

Persistent decimal rounding

  1. #1
    Forum Contributor
    Join Date
    10-16-2012
    Location
    Brooklyn
    MS-Off Ver
    Excel 2010 PC
    Posts
    137

    Persistent decimal rounding

    Hi.
    My worksheet has a column full of numbers that are suffixed with "." and more numbers.

    Example
    Before After
    135443.234 135443.23
    456544.810 456544.81
    435466.235 435466.24
    654352.8 654352.80

    I save my files as .txt. In the text files, the numbers appear preserved. But when they're opened in excel, they're rounded. If I format them as numbers with 3 decimals, the "#.8" becomes "#.800" which is no good. Oh, and the suffixes which end in a zero always end up losing the zero.

    I've formatted my columns as general, number (with 3 decimals), and as text. That hasn't worked.
    I've tried concatenating, separating the decimal from the suffix: =456544&"."&810
    I've tried using the value formula to get get the cell value.

    I don't know what to do now. Does anyone have a solution? Thanks.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Persistent decimal rounding

    Hi

    I think excel allows a max of 15 numbers, with or without a decimal place. Anything after that, it gets rounded to zero...

    123456789012345 = 123456789012345
    1234567890123456 = 1234567890123450
    12345678901234.56 = 12345678901234.50
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Persistent decimal rounding

    open the text file from excel using the import wizard, step through it but change the column data format from general to text
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Contributor
    Join Date
    10-16-2012
    Location
    Brooklyn
    MS-Off Ver
    Excel 2010 PC
    Posts
    137

    Re: Persistent decimal rounding

    All of my numbers are < 15 characters. That's good to know though, thanks.

  5. #5
    Forum Contributor
    Join Date
    10-16-2012
    Location
    Brooklyn
    MS-Off Ver
    Excel 2010 PC
    Posts
    137

    Re: Persistent decimal rounding

    Thanks Martin,

    The import wizzard works for me.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1