Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

While trying to fill in credit card numbers into Excel 2010, when I write 4645890379859736 last number becomes 0

Example:4645890379859736 is turned into 4645890379859730

Note: I can't use any - or space

Please help me.

share|improve this question

2 Answers 2

That number is bigger than the biggest integer Excel can hold as a number.

Set the cell format to text before entering the number, or type an apostrophe at the start.

share|improve this answer

Microsoft (http://support.microsoft.com/kb/269370) explained this question.

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.

Example:

If you type the number 1111222233334444 in a cell that uses the ####-####-####-#### format, Excel displays 1111-2222-3333-4440 in the cell. The actual number that you are attempting to store is 1,111,222,233,334,444, which is over one quadrillion. But because this number is so large, Excel drops the last (least significant) digit, and puts a zero in its place.

To display credit card numbers correctly, you can:

  1. Workaround suggested by Microsoft Support page.

    To work around this behavior, format the cell as text. The cell can then display up to 1,024 characters.

  2. Add an apostrophe at the beginning of the number to treat the input as text. (Reference)

    '1111222233334444
    

With these 2 methods, you can also add hyphen - or space to format as desired.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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