Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Formatting causes number loss

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi guys,
    I used excel's OpenText method to load a very large text file. The source file is ok. In excel, the number 5402563000067965 is shown as 54+Exx, which is fine. When I format it as number & did a column autofit, the last number becomes 0, it now becomes 5402563000067960. Don't know whats wrong. Help??
    Thanks.

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-25 21:15, Maslan wrote:
    Hi guys,
    I used excel's OpenText method to load a very large text file. The source file is ok. In excel, the number 5402563000067965 is shown as 54+Exx, which is fine. When I format it as number & did a column autofit, the last number becomes 0, it now becomes 5402563000067960. Don't know whats wrong. Help??
    Thanks.
    Hi Maslan:
    I don't think there is anything wrong -- I believe it has to do with EXCEL's 15 digit accuracy limit.
    Perhaps some EXCEL GURUs can shed some more light on this matter!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is it true?? I loaded the file using VBA, using the command OpenText(xxx) and I made sure all the fields are imported as text; Application.OpenText yyy yyy yyy FieldInfo:=(xxx, xxx, xxx, Array(242, 1), etc.) Why is it still treated as a number then?? Anyone else with this problem??

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Maslan

    Yes it is true, Excel (and most computers) can only store up to 15 digits accurately. After this they use zeros.

    Perhaps the link below may help

    http://www.cpearson.com/excel/rounding.htm

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi maslan
    if you want it treated like text then use array(242, 2 )
    regards Tommy

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Cheers Tommy, worked like a charm... More Excel limitations for me to know... 65K rows, 15 digit limit... what next??

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-26 01:33, Maslan wrote:
    Cheers Tommy, worked like a charm... More Excel limitations for me to know... 65K rows, 15 digit limit... what next??
    64K rows Maslan -- now that you are Excel enthusiast!

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hi

    add

    limits surprised what of 256 columns>>>>>

    I jave hurs 1000000 rows an 1024 col bit like 123 lotus via developers editiond excel???

    Is this true.....


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-26 00:49, Tommy Bak wrote:
    Hi maslan
    if you want it treated like text then use array(242, 2 )
    regards Tommy
    Hi Tommy:
    Would you please elaborate your point for me please!

  10. #10
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yogi, I think you will find Tommy is refering to the code that is generated when you use the Import Text File wizard. The last step in this feature allows you to nominate which format you wish to use on which columns, eg Text, numbers, dates etc.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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