How to work with numbers containing more than 15 digits in Excel

Numbers containing more than 15 digits in Excel are not often used, however some users might use them when recording credit card numbers, account numbers, stock codes, etc.

Applies To: Microsoft® Excel® for Windows 2013 and 2016.

Excel can’t handle more than 15 digits per cell, and so when these numbers are entered, Excel stores the first 15 digits and replaces all remaining digits with zeros.

For example; the number 1234567891234567 is stored as 1234567891234560

   

As these large numbers are NOT going to be used in a calculation, the issue is easily resolved, by simply formatting the cell to Text before entering the numbers.

Here’s how to do it:

  • Select the cells of the columns where the numbers will be stored.
  • On the Home tab, select the Number Format drop-down.

  • Scroll all the way to the bottom of the list and select Text.

  • Enter required numbers as usual.

A note to remember: if you copy and paste numbers from a different area to these formatted cells, you’ll need to use Paste Special Values or Paste as Text to retain the Text format in the cells. However, if used correctly, this will enable you to use more than 15 digits in Excel.


Sage © Sage South Africa Pty Ltd 2016 . All Rights Reserved.