Skip to main content

How to Type More than 15 Digits in MS Excel?




If you are a regular user of Microsoft Excel then you might be in danger while typing more than 15 digits in a cell. If you type 1111222233334444 then it will be 1111222233334440. Or if you type 25127680876675435 then it will be 25127680876675400. Simply the digits after 15th place will be changed to zeros! Doesn't it seem peculiar? 


Today I will explain when & why this problem occurs and how you can solve it easily. 


When the Problem Occurs?

The problem occurs when you try to type more than 15 digits in a cell like below: 

####-####-####-####
1234324545679876

But when you press Enter, the output will be 1234324545679870. Excel changes the last digit to a 0. And why someone needs to type more than 15 digits in a cell? It can't be a currency figure. 

Usually when you type a Credit Card/ Debit Card/ BO Account Number you have to type 16 digits. And the calculation of excel doesn't allow you to type more than 15 digits. That's why it converts the last digits (after 15th place) into zeros.  


Reason

You may ask - why Excel restricts users to type 15 digits in a cell? I'm also searching for a satisfactory answer. I found the following reason from Microsoft Support: 

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. 

I don't know what does it mean! :) Anyway, I can show you several ways to get rid of this problem . . . 


Solution

There are several ways through which you can avoid this problem. First I'd like to show you an easy way:

Method 1: Putting Inverted (') Before the Number

When you type more than 15 digits in a cell, just place an inverted comma ( ' ) before that number. And that mark will not be displayed on the sheet after pressing Enter! Though it will be displayed in the formula bar. Look at the image below: 


I don't know why the inverted mark disappears automatically. But I can explain why the 16th digit doesn't change to 0. Because when you type ' before a number then Excel doesn't consider this value as Number. It maybe consider the cell as text. That's why it remains unchanged. 


Method 2: Format the Cell as Text

Maybe the 2nd method is more useful. Before typing more than 16 digits in a cell, you have to format those cells as text. 

You need to select the cells first. Office 2003 users will find the Format Cell options from the Format Menu > Cells > Format Cells. And Office 2007 and 2010 users will get this as below: 


Format Cells Menu in Excel 2010


Format cell window is same in all version of MS Excel. After selecting the cells, you will just need to choose the Text option and then hit OK. That's it. 

Now you will be able to type more than thousand characters in a single cell! 


Applies to: 

All versions of Microsoft Excel. 


Stay with Marks PC Solution to get more interesting IT topics!

Labels:
Location: Dhaka, Bangladesh

Comments

  1. Kalpesh KalekarAugust 13, 2013 2:00 am

    In Excel, the floating point type is Double which is a IEEE 64-bit (8-byte) floating-point number. These can display 15 digit precision (well sort of, see the MSDN article Excel Worksheet and Expression Evaluation).

    The reason is the limited precision that can be stored in a floating point variable. For a complete explanation you should read the paper "What Every Computer Scientist Should Know About Floating-Point Arithmetic", by David Goldberg, published in the March, 1991 issue of Computing Surveys.

    Another, more accessible site, is Chip Pearson's site.

    ReplyDelete
  2. anujApril 26, 2015 9:11 pm

    Thank u

    ReplyDelete
  3. Sanjeev ZopfanSeptember 06, 2015 5:23 pm

    Similar to above discussion, my problem is thus:

    I want to type two mobile numbers in one cell of the excel with this cell/display format: #####-#####, #####-##### (means one hyphen after every 5 digits).

    But it keeps changing the last 4 digits into zeros.
    Is it possible in any way?

    ReplyDelete
    Replies
    1. Md. Abdur RahamanSeptember 06, 2015 6:43 pm

      Why don't just follow the 2nd method? Format the cell as text as shown in the image. Then you can type as many digits as you want . . . :)

      Delete
  4. UnknownJanuary 30, 2016 2:08 pm

    Thnk u but, after save as the reopen this one it's showing same format, it;s mean last number is zero

    ReplyDelete
  5. UnknownJanuary 30, 2016 2:09 pm

    Thnk u but, after save as the reopen this one it's showing same format, it;s mean last number is zero

    ReplyDelete
  6. UnknownJune 24, 2016 3:46 pm

    Before put any number in your sheet just right click select the row/column where you wanna put the numbers (more than 15 digit numbers)and choose format cells.... and choose text from the list and simply click on ok button than here you go... thanks.

    ReplyDelete
    Replies
    1. Md. Abdur RahamanJune 24, 2016 5:36 pm

      Yeah that's the short way. Thanks for the comment ...

      Delete

Post a Comment

Popular posts from this blog

GTA San Andreas Cheat Code (PDF)

After Vice City, San Andreas is the most popular game released by Grand Theft Auto (GTA). Usually GTA releases role playing game. San Andreas is such a game where you can act like a person. You can do everything. You have to take part in missions and complete them.  To make your missions easier, there are lots of cheat codes. If you can properly apply these codes, the tough parts of the game will be easier. Cheat codes are somewhat like keyboard shortcuts of computer programs.  I've shared 63 cheat codes below. There were more cheat codes in my collection, but I've omitted the less important cheats. Effects or result of the cheats are on the left side, and the codes are on the right side.  You can copy the following codes in your PC. And for your convenience, I've also uploaded a PDF file of these codes. You will find the download link at the bottom.  Effect Cheat Code Adrenaline Mode MUNASEF All Cars Explode ALL...
8 comments
Read more

Joining Texts from Multiple Cells in Excel

  Programs like MS Excel is full of functions. There are many useful functions that we don't use or we don't know about. One of them is CONCATENATE. It means joining or linking. With this function you can join texts from multiple cells. Let's see how . . . Suppose you're working in an Excel Worksheet where you need to combine texts from different cells. I'm gonna show you an example. Let's consider the image below -    What do you see ? I've imputed First Name and Last Name. In Full Name column I've used the formula i.e. CONCATENATE. And in the last column I've added Mr. with the full name.    So how do you use this formula ? Here it is -  =CONCATENATE(A2," ",B2) Observe it carefully. You can add more than 02 cells with this functions. Here I've just used 02 cells. You may think what is this " " for ? You may notice that there is a space between the two i.e. " & ".  If you don't use " " then you...
Post a Comment
Read more

Running Total/ Cumulative Sum in Excel

  Hey Guys! You should have noticed that I'm writing on MS Excel for last few days! To be honest I'm trying to learn some simple and useful tricks of Excel. And I'm also trying to share my knowledge with you. In this post I've come with such a little trick which maybe very useful if you face such situation. Let's begin . . .    Running Total/ Cumulative Sum It means the summation of numbers in a list and it will be updated each time when a number is added or changes. Look at the image below -    Look at the image. There are two columns with numbers. The first one is simply the number list. But the 2nd one is the summation of the list. Each time a new number is added to the first column the second one shows the summation. How to do this?  It's not a magic. Very simple sum formula is used to perform this task. But there is a little trick. You just need to lock the first cell and then every time it will update the running total list.  Here is my list. Sim...
Post a Comment
Read more