Excel cutting off numbers

K

Kroks

Well-Known Member
Joined
May 27, 2009
Messages
158
Reaction score
19
Hi All,

I am expreiencing a weird problem in excel. We export in CSV format a log from our ticketing system. The 1 st column is the ticket number, something like,
2011030810000039
When we open it up in excel, it "rounds" the number to 2011030810000030 this happens if you enter the number manually into excel I have tested this on Excel 2010, 2007, 2003 and all of them does this.
Does anyone know of this problem, or how I can avoid this.

Thanks

Kroks
 
What if you change the cell type to Text instead of Number?

EDIT: It works in Office 2007, but when you change it to text, it throws it as 2.01103E+15, so all you do is edit it to read the original number again and it sticks. You have to do this anyway to change the 0 to a 9.
 
Last edited:
will think about it...

it is strange.

If you double click the cell - what do you see? Still the rounded number or the 9?

Find it strange that it rounds the number down by 9. It should have made the 39 >40. Remember rounding rules.

Have you tried manually typing in say 10 such numbers in a blank excel sheet to see if it's excell or perhaps something in the export file?
 
It is not a rounding problem actually, I found a article about excel not working with more than 15 numbers in a cell. Have a look at
http://forums.cnet.com/7723-6129_102-292538.html it seems that I have to make shure that the cell is being imported as a csv file. This seems to work, but it doesnt help if I have to enter a number manually.
 
Well assuming you're not going to be doing any mathematical operations on the ticket number, modify the export so that the number is prefixed with an apostrophe: '
So the number is now '123456465465468979878974564 and it will be imported as text.
 
thisgeek said:
... modify the export so that the number is prefixed with an apostrophe: ' So the number is now '123456465465468979878974564 and it will be imported as text.
Click to expand...

^^ What he said.
 
Kroks said:
It is not a rounding problem actually, I found a article about excel not working with more than 15 numbers in a cell. Have a look at
http://forums.cnet.com/7723-6129_102-292538.html it seems that I have to make shure that the cell is being imported as a csv file. This seems to work, but it doesnt help if I have to enter a number manually.
Click to expand...
It's not just Excel though...

The "double" floating point data type (specified in IEEE 754) is almost always the largest floating point type on x86 (without extensions like MMX, SSE, etc) and it has only 15 digits of decimal precision.
 
Rkootknir said:
It's not just Excel though...

The "double" floating point data type (specified in IEEE 754) is almost always the largest floating point type on x86 (without extensions like MMX, SSE, etc) and it has only 15 digits of decimal precision.
Click to expand...

Ja, happens in Open Office as well.
 
bekdik said:
So it would appear to be in the FP processor?
Click to expand...
Yep. Most processors comply with IEEE 754.

It's usually only specialised hardware that implements some other form of floating point architecture.
 
bekdik said:
Is this the same for 64-bit and 32-bit system?
Click to expand...
I'm not sure. I've never written for 64-bit before.

However, AFAIK, 64-bit only refers to the size of integer registers and not floating point registers so it could be that x64 still uses the "double" type of IEEE 754. There is a type called "long double" available in some compilers which has precision to 34 decimal digits, but it's rarely used.

Many computer math packages include arbitrary precision libraries which don't use the CPU floating point processor, eg. Mathematica & Matlab (and weirdly enough the Windows Calculator - it has arbitrary precision for most types of calculation).
 
As mentioned earlier,popping in a " " or any other nonused special character will make it import into Excel using the full string instead of trying to round it
 
select the entire excel sheet (before exporting to .CSV)
right click
format cells
number
OK

by default Excel formats cells for "general" (rounding off all numbers entered, nit very useful when using big numbers and exporting it)
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X