Display long numbers, not Exponential (Scientific) E+

[ChrizK]

Hi

Excel Subscription Product, Microsoft 365 Apps for enterprise

I often use Excel to store mobile telecoms data, MSISDN (commonly known as your phone number, 12 digits), IMSI (sim, 15 digits), IMEI (handset, 16 digits).

It has annoyed me for years that there is no simple way to set a default in Excel, to allow 'long number'.

However, I thought I was working around this, by changing the cell properties to Number (Decimal Places =0)

I have just inserted 3578210896950826 to a cell with this cell format, and noticed that it is being changed to 3578210896950820.  Can anyone give an explanation and tell me how to stop it?  There is no function (ROUND) associated with the cell, at least, not that I can see.  I have also chosen Home>Editing>Clear>Clear All, which reverts it back to E+, and re-applied the Number (0 places), resulting in the same rounding down to 20.

What on earth is happening?  Have I been blindly doing this all these years, and not noticing???

NB Just opened a 'clean' workbook.  Pasted in 3578210896950826, shown in cell as 

3.57821E+15

, the formula bar instantly shows 3578210896950820.  Of course, if I change to Number (0 decimal places), it to shows 3578210896950820.

FYI, I know I can use '3578210896950826, and choose to align to (my preferred) right, but surely this shouldn't be necessary (and I get the annoying warning that my number is stored as text, right click, Ignore Error).

It is noted, that you would have to pre-format the cell to Text BEFORE pasting in the number, otherwise it converts it, and you lose the actual number.  This is just 'silly'.

[#2]

@ChrizK 

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.

 

[#3]

@Hans Vogelaar 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)

[#4 · Accepted solution]

@ChrizK 

You may vote here Stop Excel from changing large "numbers" (actually text values) to scientific notation. for the fixing of this issue. 

[#5]

@ChrizK ... did you ever get a satisfactory answer to this problem ? I am working with 18 digit numeric fields and some of the results are weird as hell !!!

[#6]

@ChrizK  This problem has been driving me crazy for years. I finally found an article on how to do it. Check out the video there are step by step instructions.  You can thank me later.... 

https://www.loom.com/share/e271ea75a02b48bfb30cc7e0d7c11cd8

[#7]

@Pacoblanco83 

On Beta we have these options now