How to format a 20 digit number as I want in excel?
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.
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