I want to type two mobile numbers in one cell of the excel with this cell/display format: ##### - #####, ##### - #####
But it keeps changing the last 4 digits into zeros.
Is it possible in any way?
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityI want to type two mobile numbers in one cell of the excel with this cell/display format: ##### - #####, ##### - #####
But it keeps changing the last 4 digits into zeros.
Is it possible in any way?
Excel does not have numbers of such a big resolution so you see rounding in place.
You can easily verify it: Enter 12345678901234567890
, on editing you find 12345678901234500000
.
You can
'
). No auto-formatting this way.Anyway, keeping two numbers (in some expected format) in one field is generally not a good idea so you should address this first.
You can't put all 20 digits in one cell, but suppose in A1 you have 1234567890 and in B1 9876543210
you can join the two in a third column formatted as text, with a function like
=CONCATENATE(TEXT(A1;"#####-#####");", ";TEXT(B1;"#####-#####"))
and hide the first two columns.
Hope this helps
See: https://support.microsoft.com/en-us/kb/269370
Excel follows the IEEE 754 specification on how to store and calculate floating-point numbers. Excel therefore stores only 15 significant digits in a number, and changes digits after the fifteenth place to zeroes.
The article does not include Excel 365 - and this may mean the limitation does not apply to it, or that it has not been edited to include this product. It workaround to store the value as text but you could use other options as mentioned elsewhere.
Also, 64-bit editions of Office offer greater accuracy, and this may mean the problem doesn't exist there (until longer numbers are used.) Round-tripping the data through Excel 32-bit might be problematic, I've not checked it.