When we convert numbers to text in Excel, Excel automatically removes the trailing zeros after the decimal point of a number. There are various ways to keep the trailing zeros in Excel. In this article, I will be discussing these methods to convert number to text and keep the trailing zeros in Excel.

## 📁 **Download Excel File**

Download the Excel file below.

**Overview of Trailing Zeros**

Have you ever wondered about the zeros that come at the end of a decimal number? In the decimal representation of a number, trailing zeros are a series of 0s that are followed by no other numbers. Trailing zeros to the right of a decimal point, as in 12.3400, have no effect on the value of a number and can be omitted if the numerical value is all that is important.

**Learn to Convert Number to Text and Keep Trailing Zeros in Excel with These 4 Approaches**

Let us illustrate a sample datasheet to convert numbers to texts and keep the trailing zeros in Excel. Suppose the sample table contains some dessert items and we have their corresponding prices in another column. The datasheet is presented in the following table:

**Approach**

**1. Using Text Function**

The first method includes the use of **the Text function** to convert the numbers into text and retain the trailing zeros. At first, we will add decimal points to the cell values, then use a formula to keep trailing zeros in the text, and lastly, we will concatenate the results obtained. The steps are shown chronologically with illustrations:

**⬇️⬇️ STEPS ⬇️⬇️**

- Let us say that we have the list of items and their prices from the sample datasheet. We select a cell from the Price column and then add decimal points to that cell value. We select cell
**C6**. - We right-click on the cell and a Menu will appear as such. After that, we select the
**Format Cells**button.

- A
**Format Cells**window will appear. In the Decimal Places box, we increase the count to**1**. This will add a zero after the decimal place. Then we press**OK**.

- For different cells, we increase the count in different numbers by placing values such as 2, 3, 4, and 5 consecutively in the Decimal Places box to take the corresponding number of zeros after the given number. The results will be like the table shown as follows:

- We write the following formula in cell
**D5**.

`=TEXT(C5,"##0.000")`

- Now the cell
**D5**will have its numbers converted to texts with trailing zero numbers as the same as mentioned in the formula. In this case, there will be**3**zeros after the decimal point. - Now we drag the
**“F****ill handle”**on the lower right of cell**D5**and drag it down to get the results converted from the number to the text of the other items. The table below shows the results obtained.

- As a result, we get the following result.

- Now, to concatenate and write the results in another column, we use the following formula. It means that the cells
**B5**and**D5**will be joined together or concatenated by the ampersand sign**&**.

`=B5&" Price: "&D5`

- After writing the formula and obtaining the result in cell
**E5****,**we drag the**F****ill handle**icon

- The final results will be like the following table:

**📕 Read More: How to Convert Number to Text with Leading Zeros in Excel**

**Approach**

**2. Utilizing IF Function**

In a different way, we can also use **the IF Function** to convert Number to Text and keep trailing zeros. The steps in this method are as follows:

**⬇️⬇️ STEPS ⬇️⬇️**

- First, we add decimal points to the cell values similar to the first method. That is, we follow these steps:
**Right Click >> Format Cells >> Number >> Decimal Places**and select the value of the decimal place as**3**. - We select the cell
**D5**and write the following formula.

`=IF(CELL("FORMAT",C5)="F3", TEXT(C5,"0.000"), TEXT(C5, 0))`

- The
**IF**Function will see if the cell**C5**has three decimal points. If it indeed has three decimal points, the number will convert to text keeping the 3 decimal number points along with the number. And if the number doesn’t have 3 decimal points then the output text will have only the number without any trailing zeros. - The number will be thus converted to text in cell
**D5**. - Now we drag down the
**F****ill handle**icon at the bottom of cell**D5**and then all the numbers will become texts in their respective cells.

- As a result, we get the final result in the following screenshot.

- Like before, we use the concatenate symbol in the following formula and place it in cell
**E5**.

`=B5&" Price: "&D5`

- Now we drag the
**F****ill handle**icon and get the results as shown in the following table:

**📕 Read More: How to Convert Number to Text with 2 Decimal Places in Excel**

**Approach**

**3. Using Apostrophe Symbol**

Another way of converting numbers to texts is by using the apostrophe sign **(****‘****)** in front of each number and thereby keeping the trailing zeros. So anything after the apostrophe sign will be regarded as text. Here is the method illustrated in the following picture:

- Here, some errors will appear. If you click it then you will see that the number is stored as text.

**Approach**

**4. Applying VBA Code**

For those of us who are familiar with VBA Macro, we can use VBA to convert numbers into texts by using a specific programming language. The steps for this process are as follows:

**⬇️⬇️ STEPS ⬇️⬇️**

- First, we select all the cells
**D5:D9**. - Then we hover over the
**Developer**option in Excel and select**Visual Basic**. Alternatively, we can use the shortcut**ALT+F11**.

- Now we select the
**Insert**option and select**Module**.

- Then we write down the following code in the window.

```
Sub KeepTrailingZeros()
Dim NumRan As Range
Set NumRan = Selection
NumRan.NumberFormat = "#,##0.000;-#,##0.000"
End Sub
```

- After that, we click on the
**Run**command.

- We will see that the numbers in the Price column will convert into texts with trailing zeros.

- After that, we apply the following formula in cell
**D5**similar to the one used in the first method.

`=TEXT(C5,"##0.000")`

- We drag the
**F****ill handle**icon from the bottom left of cell**D5**and get the following results summed up in a table.

**Approach**

### 5. Using Combined Formula

When we want to add zeros after whole numbers across a large sheet of document, we can increase the number of trailing zeros in the following way. Here, we would like to utilize the combination of **REPT** and **LEN** functions.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, we select cell
**D5**. - Then we write the following formula down in the
**formula bar**.

`=C5&REPT("0",5-LEN(C5))`

Here **LEN(C5)** indicates the length of the value of cell **C5****. **It will have a return value of** 1** as the entry in cell **C5** is** 3** which is a one-digit number. **5-LEN(C5)** will be **4**. **REPT** function will repeat **0** four times. The **&** sign will concatenate the values of cell **C5** and the zeros added.

- Then we will see the number in cell
**C5**have four trailing zeros. - Next, we drag the
**F****ill handle**icon again to apply the formula to the rest of the cells. - The final table will be shown as follows:

**📄 Important Notes**

🖊️️ If we don’t see a **Developer Tab** in Excel, we can make it visible by going to **File>>Option>>Customize** **Ribbon**.

🖊️ We can press **ALT+F11** to open the VBA Editor.

🖊️ To bring up the **Macro Editor**, we can press the shortcut **ALT+F8**.

**📝 Takeaways from This Article**

We have taken the following summed-up inputs from the article:

📌 Using the** TEXT** function and** IF **function, we can easily convert numbers into texts by specifying the cell and the number of digits we want to take after the decimal. Alternatively, putting an apostrophe sign before any number will automatically turn that number into text.

📌 When we use VBA Codes it becomes easier to perform the changes if the amount of data is large.

**Conclusion **

This article will hopefully help you to convert numbers into texts with trailing zeros in Excel. If you have any further queries, you can comment on the article or reach out to us. Follow our page **ExcelDen **to get more insights about different Excel-based information.

## Related Articles

**6 Easy Tricks to Convert Date to Text (YYYYMMDD) in Excel****3 Ways to Convert Number to Text with Commas in Excel**