Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Fairfax, VA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Treating Large Number as Text - An Excel Bug?

    All,

    I am using Excel 2007. I have to work with 13 character long National Stock Numbers (NSNs) which are a string of numbers that I will never do arithmetic on. If I type a NSN into a cell Excel displays it in scientific notation. If I change the cell category from General to text nothing changes except it is left justified instead right. However, I very recenty just discoved that if I change the category to Text FIRST then type the NSN it display all the whole string as I want it to.

    Now my questions:

    1) Would others confirm this happens to them in Excel 2007.
    2) Does this happens the same way in Excel 2010? (I don't have access to a copy of Excel 2010.)
    3) Does anyone not consider this an honest to goodness bug?
    4) Does anyone have a way to get to the MS Excel Team and report this and ask that it be fixed?

    I know there have been similar threads about long number strings displaying as scientific notation but I think that this issues with the order of steps producing different results is a new wrinkle on this.

    Thanks,
    Bob S.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Same situation when I tried it in 2010.

  3. #3
    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
    I would not call it a "bug". The General format assumes that large numbers do not need to display with such precision and uses a (pre-programmed) more "optimal display". Change the format to a number with 0 decimals and make sure the column is wide enough if you want to display it differently. The format does not affect the number.If it is a number in the sense that no calcs will be done, then formatting as TEXT is a better way to go. This will also allow more digits (Credit card "numbers" of 16 digits get the last digit truncated if kept as a number since excel has only 15 digits of precision in numbers). A way to ensure that by default it will be text is to format first, but I prefer just appending a letter to the start of the number. Putting in NSN before the 13 digit number will fix it.

    [After changing to/from TEXT the value needs to be confirmed again. Goto Edit mode and re-enter the value and it should change the formatting]

    Steve

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

    This happens in all versions of Excel.
    It is not a bug.
    It is intended to be this way.

    zeddy

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Or you can just prefix the number with an apostrophe {'} when you enter it and the number will display w/o the apostrophe.
    ExcelApost.JPG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    Fairfax, VA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    All,

    Thanks for the work arounds but I already know them. The issue for me is, is this a bug and what to do about it. To clarify what I consider the bug is that doing the same steps in different orders produces different results. If that happened in code I wrote my clients would consider that a bug and want it fixed. So Zeddy in light of my clarification would you still say this is intended to be this way?

    Anyone have a suggestion on what is the best way to contact the MS Excel team and see what they say about this?

    Thanks,
    Bob

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

    This is definitely not a bug. It is a design decision much as early versions of Excel only had 256 columns and 65535 rows. Memory was limited then so trade offs {design decisions} had to be made. the same is true of number formats. When you write code you probably select the appropriate size for a given numeric variable {Integer, Long Integer, Single, Double}. Each of these allows numbers to a certain maximum size and precision. You do this because declaring all numbers Double uses up too much memory. Well, the MS engineers made the same determination about the size and precision of numbers which would be displayed in Excel to maximize the memory and performance characteristics of the application, a design decision. Don't get me wrong I'm no MS Fan Boy by any stretch of the imagination but I just don't see how this could be called a bug. It is a program who's design does not meet your specified needs just like a sports car doesn't meet the specified needs of someone who needs to haul ladders and tool boxes around, they get a pickup truck.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    Super Moderator BATcher's Avatar
    Join Date
    Feb 2008
    Location
    A cultural area in SW England
    Posts
    3,414
    Thanks
    33
    Thanked 195 Times in 175 Posts
    Have a look at a couple of pieces of light reading: Floating-point arithmetic may give inaccurate results in Excel and Numeric precision in Microsoft Excel. See especially the References in the second article.

    These back up Mr R Geek's previous post!
    BATcher

    Time prevents everything happening all at once...

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi rmsiegel

    Re: what I consider the bug is that doing the same steps in different orders produces different results

    If no cell formats have been set by you, Excel will use a default setting.
    Numbers are aligned to the right, text aligned to the left.
    When you type in your 13-digit number first, Excel will automatically enter this by default in scientific notation and align it to the right. If you then decide to set the cell format to Text, Excel will now simply align this to the left, but it will STILL be a 'number'. You can check this by using the formula =ISNUMBER(cell) which will show the result of TRUE. (It doesn't matter whether you do this with 4 digits or 13 digits, whatever number you entered is just moved to the left.
    Now, if you set the cell format to Text, then whatever you then type, whether it is 4 digits or 13 digits or whatever, it will be treated purely as text, and of course will now be aligned to the left, but will now show all the characters you typed (even fifty digits etc). However, if you use the formula =ISNUMBER(cell) where cell is the cell address containing your entry, it will now show the result as FALSE
    So, yes, doing the steps in different order will give you a different result. If you do change the order of steps, you shouldn't expect to always get the same result. For example, when you fall off a fifty foot ladder, it is better to be on the bottom rung.

    zeddy

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by zeddy View Post
    For example, when you fall off a fifty foot ladder, it is better to be on the bottom rung.
    Don't make much difference when the ladder's in free fall...
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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