Forum Discussion
Display long numbers, not Exponential (Scientific) E+
You may vote here Stop Excel from changing large "numbers" (actually text values) to scientific notation. for the fixing of this issue.
In Excel specifications and limits you can see that Excel's number precision is 15 digits:
Items such as MSISDN, IMSI and IMEI are not really numbers, but text strings consisting only of digits. You'll never use them in calculations such as SIN or SQRT.
So you should either format the cells where you enter such items as Text before data entry, or prefix the values with an apostrophe '. The latter forces Excel to treat a value as text even if the cell is not formatted as Text.
This will also allow preserving leading zeros.
HansVogelaar HI, thanks for the speedy reply.
As mentioned, I know how to get around the problem. I must apologies, as I confused the main issue, that being the conversion.
I can 'live with the limitation' of 15 digits (although I find it annoying I can't choose the limit), but pre-formatting cells is a pain, and I would prefer not to.
My preference would be to select the cells once they have been populated, and 'convert' to text.
The problem is that my values are being altered, as described.
In my opinion, this is completely wrong. In some circumstances, it may be useful to round to the nearest ten, but it most certainly should be by choice, and not imposed without any method to keep the true value.
Can someone explain why this is happening, and how to stop it?
(I expect it is being forced in the same way exponential is forced, so that any number over 15 digits facilitates E+)
BTW just because I have explained where these numbers(!) are coming from, does not exclude them from being numbers. For my purposes, I may want to create a sequence, simply adding 1, or steps of 5. My use of Excel should not need any justification.
Actually, if it is 15 digits, why is 447123456789 changed to
| 4.47123E+11 |
? (11 digits are maintained, ie the cell shows the real value)