Formatting causes number loss

[Maslan]

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.

 

[Yogi Anand]

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!

 

[Maslan]

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??

 

[Dave Hawley]

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

 

[Tommy Bak]

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

 

[Maslan]

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

 

[Yogi Anand]

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!

 

[Jack in the UK]

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.....

 

[Yogi Anand]

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!

 

[Dave Hawley]

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.