Formatting causes number loss

Maslan · Mar 25th, 2002, 10:15 PM

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 · Mar 25th, 2002, 10:26 PM

Quote:

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 · Mar 25th, 2002, 11:19 PM

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 · Mar 25th, 2002, 11:44 PM

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 · Mar 26th, 2002, 01:49 AM

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

Maslan · Mar 26th, 2002, 02:33 AM

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

Yogi Anand · Mar 26th, 2002, 04:03 PM

Quote:

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 · Mar 26th, 2002, 04:08 PM

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 · Mar 26th, 2002, 09:28 PM

Quote:

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 · Mar 26th, 2002, 10:25 PM

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.

Mar 26th, 2002, 04:08 PM	#8
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,065	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"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 25th, 2002, 10:15 PM	#1
Maslan New Member Join Date: Mar 2002 Posts: 11	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.

Mar 25th, 2002, 11:19 PM	#3
Maslan New Member Join Date: Mar 2002 Posts: 11	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??

Mar 25th, 2002, 11:44 PM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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

Mar 26th, 2002, 01:49 AM	#5
Tommy Bak Board Regular Join Date: Feb 2002 Location: Guderup, Denmark Posts: 287	Hi maslan if you want it treated like text then use array(242, 2 ) regards Tommy

Mar 26th, 2002, 02:33 AM	#6
Maslan New Member Join Date: Mar 2002 Posts: 11	Cheers Tommy, worked like a charm... More Excel limitations for me to know... 65K rows, 15 digit limit... what next??

Mar 26th, 2002, 10:25 PM	#10
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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.