4

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?

  • Accidentally flagged this as I thought I was still on SO, D'Oh! Ignore my flag please! – AStopher Sep 6 '15 at 18:50
  • 3
    There are lots of places where long strings of digits are used, like phone numbers, credit card numbers, etc., where the numbers are never used for math purposes. As a general rule, you are better off storing them as text to avoid problems like this, or conversion to scientific notation, deletion of leading zeros, and other things that happen to numerical values. – fixer1234 Sep 6 '15 at 22:47
  • See also Adding more than 15 digits in Excel. – Scott Nov 8 '17 at 0:59
13

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.

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.