Custom format some 16 9s, convert last digit to zero in the cell

Posted on 2013-12-12
Medium Priority
Last Modified: 2013-12-12

In Excel, I want to write a 16 number bank account number. So when in custom format using Format Cells, I use 16 0s (0000000000000000), then while I write a bank account number in the cell ending with 9, then last number changes to zero.

Example: 1234567890123459 changes to 1234567890123450 - why does this happen I don't understand.

Will appreciate a fast reply.

Question by:Sanjay Gandhi
  • 2
LVL 19

Expert Comment

ID: 39714545
Excel has a maximum precision of 15 significant digits, which means that for any number containing 16 or more digits, such as a credit card number, the last digit is rounded down to zero.

You can do one of two things:

1. Use the apostrophe character
For a small set of number codes, you can type an apostrophe character (') in front of the number code and then add back the leading zeros.

2. Convert the number to text
LVL 19

Accepted Solution

jss1199 earned 1200 total points
ID: 39714546
Meant to detail how to change to text.

On the Home tab, click the Dialog Box Launcher Button image next to Number.

In the Category box, click Text.

Author Closing Comment

by:Sanjay Gandhi
ID: 39714568
Thanks, works fine.

- San.

Featured Post

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article, I will demonstrate that how to do a PST migration from Exchange Server to Office 365. This method allows importing one single PST, or multiple PST's at once.
Read this tutorial to learn how to fix repeating password error prompts when setting up Gmail IMAP with Microsoft Outlook. The entire process is described with step by step, illustrated instructions. Enjoy...
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question