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.

**Read More:** [Fixed!] Why Is Excel Changing My Numbers?

### 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.

**Read More:** How to Stop Excel from Auto Formatting Numbers

### 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.

**Read More:** How to Stop Excel from Rounding 16 Digit Numbers

### 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 “**400055558877220**1″ 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**

## 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.

**Related Articles**

- Why Excel Is Changing My Numbers to Decimals
- How to Stop Autocorrect in Excel for Dates
- [Fixed!] Excel Changing Dates to Random Numbers
- How to Stop Excel from Changing Numbers to Scientific Notation
- How to Stop Excel from Auto Formatting Hyperlinks
- How to Stop Excel from Changing Numbers to Dates
- How to Prevent Cell Format Changes Automatically in Excel
- Why Is Excel Changing My Numbers to Zero?

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