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.


Download Practice Workbook


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.


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

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.

Excel 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 quotation mark (‘).

Let’s see the steps.

Steps:

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

Using Single Quotation Mark

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.

Use of Single Quotation Mark to Stop Excel from Changing Last Number to 0

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

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

After that, a drop-down menu will appear.

  • Thirdly, select Ignore Error.

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

Read More: [Fixed!] Why Is Excel Changing My Numbers? (4 Reasons)


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:

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

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

  • Secondly, Right-click on the selected cells.
  • Thirdly, select Format Cells.

Changing Formato of the Cell to Stop Excel from Changing Last Number to 0

Here, a dialog box named Format Cells will appear.

  • Firstly, select Text from Category.
  • Secondly, 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 got 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.

Read More: How to Stop Excel from Auto Formatting Numbers (3 Easy Ways)


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.

Use of Legacy Wizards Feature in Excel

 

Let me show you the steps.

Steps:

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

  • Firstly, go to the File tab.

  • Secondly, select Options.

Here, the Excel Options dialog box will appear.

  • Firstly, go to the Data tab.
  • Secondly, check the From Text (Legacy) option.
  • Thirdly, select OK.

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

Adding From Text (Legacy) to Stop Excel from Changing Last Number to 0

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

  • Firstly, select the cell from where you want to insert Account No.
  • Secondly,  go to the Data tab.
  • Thirdly, select Get Data.

Using Get Data Command to stop Excel from Changing Last Number to 0

After that, a drop-down menu will appear.

  • Firstly, select Legacy Wizard from the drop-down menu.
  • Secondly, select From Text (Legacy).

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

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

Imposting Text File to Stop Excel from Changing Last Number to 0

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.

Text Import Wizard Dialog Box to Stop Excel from Changing Last Number to 0

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.

Import Data Dialog Box to Stop Excel from Changing Last Number to 0

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

Read More: How to Stop Excel from Rounding 16 Digit Numbers (2 Easy Ways)


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:

  • Firstly, select the cell where you want to insert Account No. Here, I selected cell D5.
  • Secondly, in cell D5 write the following formula.
=T("4000555588772201") 

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

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.

  • Thirdly, 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.

Read More: How to Stop Excel from Changing Numbers (3 Easy Methods)


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.

How to Add Leading Zeros in Excel

Let’s see the steps.

Steps:

  • Firstly, select the cell where you want to insert the Zip Code. Here, I selected cell E5.
  • Secondly, in cell E5 write the following formula.
=TEXT(D5,"00000")

Using TEXT Function to Add Leading Zeros

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.

  • Thirdly, 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.

Practice Sheet for How to Stop Excel from Changing Last Number to 0


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. To get more articles like this stay connected with ExcelDemy. Lastly, if you have any questions feel free to let me know in the comment section below.


Related Articles

Mashhura

Mashhura

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo