Why does Excel change my numbers to 0

We all know that Excel can be a powerful tool, however in an effort to anticipate the need of a user, Excel can also make it difficult to create spreadsheets for importing data into Acctivate QuickBooks Inventory Software.  You may have entered a string of numbers in Excel only to find they were reformatted to a date or something else and no longer display what you intended.  Importing or updating records in a database most often the data must match 'exactly'.   Therefore when Excel changes the format of your import data your import will fail.  This can be extremely frustrating but do not worry you have a few options to easily correct it.

The Apostrophe Method 

By placing an Apostrophe ( ' ) preceding the number, you will prevent Excel from reformatting the number and display the number exactly how you have it entered.  

Example: 

• Entering   '1/2    into a cell it will prevent Excel from making your fraction a date. 

This method means that even if someone were to change the cell formatting back to General and tries to edit a cell it will continue to look the same  instead of being auto-formatted by Excel.

• Type an Apostrophe in the Cell
• Enter the number as you want it to display and press Enter
• Now the numbers should display and import correctly
• This will allow you use MATCH and VLOOKUP functions in Excel  (Apostrophe will be ignored)

Format as Text Method

The second most common method to simply format the entire worksheet or a group of cells as Text before you enter your data.  Formatting cells to display as text will make Excel display the data exactly as it has been entered. (Remember to format your cells Before you input your data)

When you paste or type a number with a certain length into Excel, Excel changes last digit to zero. This happens when the number is longer than 15 digits. However, there is an easy way to overcome this issue.

Here is a number with 16 digits inputted into cell A1:

When you press Enter button, it will be displayed as below:

It is not only the display that changes, excel changes last digit to zero too.

When your Excel changes last digit to zero, easiest and fastest solution is:

Select the cells that will host these numbers and format them as text as shown below.

Paste or input your long numbers.

Now your number is stored as text. You are free from digit restriction and your number is displayed as it is. An exclamation mark will pop up next to the cell whenever you select it. That is caused by storing a number as text data. Just ignore it and you will be fine. If you convert those values to number, it reverts back to previous state and Excel changes last digit to zero.

Hello Excellers, welcome to another blog Excel post in my #ExcelTips 2021 series. Today let’s look at why Excel Is changing the last digit of a number to zero. That’s correct Excel changes the last digit to zero sometimes. If you have ever come across this, it can be confusing and damn frustrating. You type a long number into Excel, and weirdly anything past digit 15 appears as zeros. So if you are entering a credit card number, which is usually 16 digits, then the last is displayed as a zero.

Because Excel only stores 15 significant digits in a number, Excel changes any remaining digits to zeros. As the formatting of the cell is numbers, Excel interprets the numbers as being intended for calculation. Okay, so can we reformat them as Text or something else? Well, no, once entered, you cannot just reformat. Instead, you will need to format new cells as Text then enter the numbers again.

The Solution To Solve Why Excel Is Changing The Last Digit To Zero.

Hmmm, so is there anything else we can do to avoid having to re enter all of those numbers?…there are a few things.

Solution 1. Leading Apostrophe.

The first solution is to insert a leading ‘in front of you number. So, for example ‘2560089996999987 would display correctly. You can see in the screen grab below without the apostrphoe Excel changes the digit to zero.

Solution 2. Using The Text Import Wizard.

Now, onto the second solution. If you are importing your data from a .rtf or.csv file, when the text import wizard appears, scroll to your field containing the offending ‘longer than 15 digits’ number. Click on it, then in the upper right hand section of the window, change the field of column type from ‘General’ to ‘Text’.

Note. The Text Import Wizard is a legacy function. If you are using esrlier version of Execl then you will be ok. If you have a newer version of Excel you will see the Get & Transform Tools. So since In Excel 365 and 2016 the “Text Import Wizard" has been removed. But, if you want to use the legacy version of Text Import Wizard you can get it back.

Restore The Text Import Wizard.

Follow the steps below to restore the original text Imprt Wizard.

• FIle | Options | Data
• Select Show Legacy Data Import Wizards
• Hit Ok

You can now go ahead and use the Legacy Data Import feature to comportably import your data the way you want to.

Next select your file to import. After selection hit Import.

I always make sure to check out any data I am importing to see if this issue may occur. It is easier to catch the issue at import stage than having to either get a leading apostrophe or re type. Who wants that right?.

How do I stop Excel from changing my numbers?

Why is my Excel formula giving me 0?