number formatted cell
If I am typing in 19 numbers in a cell (that has been formatted to number) to
keep track of account numbers, it puts zeroes at the end and only 15 numbers are showing. If I go into another cell and choose text first before typing and then type my numbers you can see all the numbers, but when I try to go and change the number formatted cell to text the number still does not change unless I retype the number. Is this the way this works? |
yes
excel only keeps track of the first 15 digits in a number, if you entered more digits as a number,they are now lost. "Pam Coleman" wrote: If I am typing in 19 numbers in a cell (that has been formatted to number) to keep track of account numbers, it puts zeroes at the end and only 15 numbers are showing. If I go into another cell and choose text first before typing and then type my numbers you can see all the numbers, but when I try to go and change the number formatted cell to text the number still does not change unless I retype the number. Is this the way this works? |
yes... with floating point numbers the way most computers store them (at the
level you and I would care not to think about ;-) ) space to store them is regimented. I copied and pasted the definition of the double data type from excel VBA. Generally speaking, a number in excel can hold 15 significant digits... so once you type in the number and excel determines it needs to be stored as a numeric, and digits after the 15th are gone.. truncated. It sort of makes sense.. if you're doing a calculation with the number, any digit past the 16th would not impact the result much. BUT it does make for the entry of numerics used for identification tricky. My basic rule of thumb (in databases and spreadsheets ) is if you are NOT going to do a calculation with the number ( +, -, *, / ) force it to be text. hope this helps.. Double (double-precision floating-point) variables are stored as IEEE 64-bit (8-byte) floating-point numbers ranging in value from -1.79769313486231E308 to -4.94065645841247E-324 for negative values and from 4.94065645841247E-324 to 1.79769313486232E308 for positive values. The type-declaration character for Double is the number sign (#). "Pam Coleman" wrote: If I am typing in 19 numbers in a cell (that has been formatted to number) to keep track of account numbers, it puts zeroes at the end and only 15 numbers are showing. If I go into another cell and choose text first before typing and then type my numbers you can see all the numbers, but when I try to go and change the number formatted cell to text the number still does not change unless I retype the number. Is this the way this works? |
All times are GMT +1. The time now is 10:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com