How to Stop Excel from Changing Last Number to 0 (4 Easy Ways)

If you are wondering why Excel is changing your last number to 0 and how you can eliminate this problem, then this article is for you. The main objective of this article is to explain how to stop Excel from changing last Number to 0.

Why Does Excel Change Last Number to 0?

There is a limited number of digits you can store in Excel. Excel can not store numbers that have more than 15 digits. It changes the 16th digit to 0. In fact, it turns every number to 0 after 15 characters in a number.

But there are some cases where you will need to insert numbers that contain more than 15 digits. This article will show you how you can insert these numbers.

How to Stop Excel from Changing Last Number to 0: 4 Easy Ways

Here, I have taken the following dataset to explain this dataset. It contains the First Name and Last Name of 6 people and their Account No. I entered data for everyone. I entered Account No. from 4000555588772201 to 4000555588772206. But Excel has changed the last number to 0 and for that every Account No. is looking the same.

Now, I will explain 4 easy ways to stop Excel from changing last number to 0.

1. Using Single (‘) Quotation Mark

In this first method, I will show you how you can stop Excel from changing the last number to 0 by using the single apostrophe (‘).

Let’s see the steps.

Steps:

First, select the cell where you want to write Account No. Here, I selected cell D5.
Next, in cell D5 enter a single quotation mark (‘) and then write the number.
Then, press ENTER.

After that, you will see that the last number has not changed to 0 and the single quotation mark is also not showing.

Finally, enter the other Account No. in the same way.

In the following picture, you can see that the last number has not changed to 0 and I have entered all the Account No. as I wanted. But an Error Warning is showing here.

Now, I will explain how you can remove the Error Warning.

First, select the cells where the Error Earning is showing. Here, I selected the cell range D5:D10.
Next, click on the Error Warning.

After that, a drop-down menu will appear.

Then, select Ignore Error.

Finally, you will see that you have removed the Error Warning and got your desired dataset.

2. Employing Format Cells Feature to Stop Excel from Changing Last Number to 0

Now, I will explain how you can stop Excel from changing the last number to 0 by employing the Format Cells feature. Let’s see how it is done.

Steps:

First, select the cells where you want to enter Account No. Here, I selected the cell range D5:D10.

Next, Right-click on the selected cells.
Then, select Format Cells.

Here, a dialog box named Format Cells will appear.

First, select Text from Category.
Then, select OK.

Now, simply write Account No. in your desired cell. Here, I wrote my first Account No. in cell D5.
Next, press ENTER.

Finally, you will see that Excel has not changed the last number to 0 and you have your desired Account No.

After that, enter the other Account No. in the same way.

Here, you can see that an Error Warning is shown in the following picture.

Finally, remove the Error Warning by following the steps from Method-01.

3. Use of Legacy Wizards Feature in Excel

In this method, I will use the Legacy Wizards feature to stop Excel from changing the last number to 0. For this example, I have written Account No. in a Text File. I will import this file to Text Import Wizard to get Account No. without changing the last number to 0.

Let me show you the steps.

Steps:

To begin with I will add the Legacy Wizards (From Text) to my Excel.

First, go to the File tab.

Next, select Options.

Here, the Excel Options dialog box will appear.

First, go to the Data tab.
Next, check the From Text (Legacy) option.
Then, select OK.

Now, From Text (Legacy) will be added to your Excel.

Now, I will import data from the Text File I created.

First, select the cell from where you want to insert Account No.
Next, go to the Data tab.
Then, select Get Data.

After that, a drop-down menu will appear.

First, select Legacy Wizard from the drop-down menu.
Next, select From Text (Legacy).

Now, a dialog box named Import Text File will appear.

First, select the Text File in which you have stored your data. Here, I selected the file named TextFile.
Next, select Import.

Now, the data will be imported to Text Import Wizard. Here, you will see the Text Import Wizard – Step 1 of 3 dialog box has appeared.

After that, select Next.

Then, the Text Import Wizard – Step 2 of 3 dialog box will appear.

Now, select Next.

After that, the Text Import Wizard – Step 2 of 3 dialog box will appear.

Next, select Text.
Then, select Finish.

Now, the Import Data dialog box will appear. Here, you will see that the cell you selected before is already entered in your “Where do you want to put the data?” section.

After that, select OK.

Finally, you will see that you have entered all the Account No. and the last numbers have not changed to 0.

4. Applying T Function to Stop Excel from Changing Last Number to 0

Here, I will show you how you can use the T function to stop Excel from changing the last number to 0. Let’s see the steps.

Steps:

First, select the cell where you want to insert Account No. Here, I selected cell D5.
Next, in cell D5 write the following formula.

=T("4000555588772201")

Here, in the T function, I wrote “4000555588772201″ as the value. Now, the formula will return the value as I have inserted the number in a double quotation mark and it refers to a text string.

Then, press ENTER to get Account No. and the last number will not change to 0.

Finally, enter the rest of Account No. in the same way.

How to Add Leading Zeros in Excel

Whenever you enter a number with leading zeros, Excel automatically removes the leading zeros and keeps the rest of the digit. But in some cases, you will need to keep the leading zeros. In this section, I will explain how to add leading zeros in Excel.

To explain this section, I have taken the following dataset. It contains the First Name and the Last Name of some people and the Zip Code of the area they live in. The Zip Code contains 5 digits. Leading Zeros are added to the Code to make it a 5-digit number. Here, I will explain how you can add these leading zeros.

Let’s see the steps.

Steps:

First, select the cell where you want to insert the Zip Code. Here, I selected cell E5.
Next, in cell E5 write the following formula.

=TEXT(D5,"00000")

Here, in the TEXT function, I selected D5 as the value and “00000” as format_text. Now, the formula will add leading zeros to the number in cell D5 and return a 5-digit number.

Then, press ENTER to get the result.

Now, drag the Fill Handle to copy the formula.

Finally, you can see I have copied the formula and added leading zeros where it is needed.

Things to Remember

You have to remember that the T function returns a value when the value is a text or refers to a text. Otherwise, it returns empty text. So, you must enter the number in double quotation marks (“”) to refer it to a text.

Practice Section

Here, I have provided a practice sheet for you to practice how to stop Excel from changing the last number to 0.

Download Practice Workbook

Number Turning to Zero.xlsx

Conclusion

In this article, I tried to explain how to stop Excel from changing last number to 0. Here, I explained 4 easy and effective ways of doing it. I hope this was clear to you. Lastly, if you have any questions feel free to let me know in the comment section below.

<< Go Back to Excel Auto Formatting | Excel Cell Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Stop Excel from Changing Last Number to 0 (4 Easy Ways)

Why Does Excel Change Last Number to 0?

How to Stop Excel from Changing Last Number to 0: 4 Easy Ways

1. Using Single (‘) Quotation Mark

2. Employing Format Cells Feature to Stop Excel from Changing Last Number to 0

3. Use of Legacy Wizards Feature in Excel

4. Applying T Function to Stop Excel from Changing Last Number to 0

How to Add Leading Zeros in Excel

Things to Remember

Practice Section

Conclusion

Related Articles

Leave a reply Cancel reply