5

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?

miroxlav
  • 14,845
zopfan
  • 57

3 Answers3

15

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

  • Use less digits: split long number into two columns (like prefix + number or
    number1 + number2).
  • Format number as text: start number with apostrophe ('). No auto-formatting this way.
  • Use standard database instead of Excel: inside Microsoft Office, you can try Microsoft Access. Remember that Excel is primarily a spreadsheet processor, not a database. Microsoft Access offers similar template-formatting for longer data.

Anyway, keeping two numbers (in some expected format) in one field is generally not a good idea so you should address this first.

miroxlav
  • 14,845
2

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

Raffaele
  • 21
1

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.

James Snell
  • 356