While converting numbers to text in Excel, you will find that Excel removes the trailing zeros after the decimal point of a number. There is a simple solution to keep the trailing zeros of a number while converting it to text in Excel. In this tutorial, I will show you how to convert number to text and keep trailing zeros in Excel.

## How to Convert Number to Text and Keep Trailing Zeros in Excel: 4 Easy Ways

Letâ€™s consider a situation where we have an Excel worksheet that contains information about different fruits and their price. I will use this worksheet to show you how to convert number to text and keep trailing zeros in Excel. The image below shows the worksheet that we are going to work with.

### Method 1: Convert Number to Text and Keep Trailing Zeros in Excel Using the TEXT Function

The easiest way is to use** the TEXT function** in Excel to convert numbers to text and keep trailing zeros. Letâ€™s see how we can do that.

**Step 1:**

- First, we will select a cell in the
**Price**column to**add decimal points**to that cell value. For example, we have selected cell**C6**. - Then, we will
**right-click**on that cell. A**menu**will appear. We will click on the**Format Cells**from that window.

- A window titled
**Format Cells**will now appear. We will**increase**the**Decimal Places**to**1**to add a**zero**(**0**)**after**the**decimal place**. - Then, we will click on
**OK**.

- We will
**increase**the**decimal places**to**different numbers**for**different values**of the**Price**.

**Step 2:**

- Then, we will write the following formula in cell
**D5**.

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

**Formula Breakdown: **

Using the** TEXT **function, you can **apply formatting **to a** number **withÂ **format codes** toÂ **change **the way it**Â **appears.

- Now, we will see that the
**number**in cell**C5**has been**converted to text**with the**trailing zeros following**the**decimal point**.

- Now, we will drag the
**fill handle**of cell**D5**to apply the formula to the rest of the cells.

- Finally, we will see that all the
**numbers**in the**Price**column have been**converted to text**with the**trailing zeros following**the**decimal point**.

**Step 3: **

- Now, we will write down the below formula in cell
**E5**.

`=B5&" Price: "&D5`

**Formula Breakdown:**

**Ampersand sign** (**&**) will **concatenate** the** texts** in cells **B5** and **D5** with the text** “ Price“**

**between**the

**two texts**.

- We will now see that
**texts**in cells**B5**and**D5**have been**concatenated**using the**ampersand**(**&**) sign.

- Now, we will drag the
**fill handle**of cell**E5**to apply the formula to the rest of the cells.

- Finally, we will see that respective
**texts**in the**Product**and**Number to Text**columns have been**concatenated**using the**ampersand**(**&**) sign.

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

### Method 2: Use the IF Function to Convert Number to Text and Keep Trailing Zeros in Excel

Alternatively, we can also use the **IF function** to convert numbers to text and keep trailing zeros in Excel. We have to follow the below steps.

**Steps:**

- First, we will
**add decimal points**to the cell values in the**Price**column as we have done in the**first method.** - Then, we will write the following formula in cell
**D5**.

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

**Formula Breakdown: **

**IF function**will check if the**number**has**3 decimal points**.- If the number has
**3 decimal points**, the**TEXT**function will**convert the number to text**while**keeping**those**3 decimal points**with the number. - And if the number
**does not have 3 decimal points**, then the**TEXT**function will**convert**the**number**to**text**without**changing anything**.

- Now, we will see that the
**number**in cell**C5**has been**converted to text**with the**trailing zeros following**the**decimal point**.

- Then, we will drag the
**fill handle**of cell**D5**to apply the formula to the rest of the cells.

- Finally, we will see that all the
**numbers**in the**Price**column have been**converted to text**with the**trailing zeros following**the**decimal point**.

- Then, if we use the same
**concatenation**formula using the**ampersand**(**&**) sign, we will see that respective**texts**in the**Product**and**Number to Text**columns have been**concatenated**.

### Method 3: Convert Number to Text and Keep Trailing Zeros Using the Apostrophe Symbol

Another easy way to convert numbers to text and keep trailing numbers in Excel is to insert **apostrophe signs** in front of the numbers. We have to do the following.

**Steps:**

- We will put an
**apostrophe sign**(**â€˜**) in front of each number in the**Price**That will**convert the number to text**while**keeping trailing zeros**.

**Read More:** How to Convert Number to Text with Commas in Excel

### Method 4: Use a VBA Code to Convert Number to Text and Keep Trailing Zeros in Excel

If you are familiar with the **VBA m****a****cro**, you can use the **VBA **code to **convert numbers to text** and keep **trailing zeros** in Excel. We will follow the below steps to do that.

**Step 1:**

- First, we will select
**all the cells**in the**Price**column. - Then, we will select
**Visual Basic**from the**Developer**. We can also press**ALT+F11**to open it.

- Now, click on the
**Insert**button and select**Module**.

- Then, write down the following code in the window that appears.

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

- Then, click on the
**Run**(**▶**).

- Finally, we will see that all the
**numbers**in the**Price**column have been**converted to text**with the**trailing zeros following**the**decimal point**.

**Step 2:**

- Next, we will write down the same
**TEXT**formula we have used in**method 1**in cell**D5**to**convert the number**in cell**C5**to**text**.

- Upon dragging the fill handle downward to apply the formula to the rest of the cells, we will see that all the
**numbers**in the**Price**column have been**converted to text**with the**trailing zeros following**the**decimal point**.

### Bonus: Add Trailing Zeros Using Formula in Excel

If you have a large worksheet where you need to **add a specific number of trailing zeros** after each number, then you can use a **formula **to **add trailing zeros** instead of writing zero after each number** manually**. Letâ€™s see how we can do that.

**Steps:**

- First, we will write the following formula in cell
**D5**.

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

**Formula Breakdown: **

**LEN****(C5)**will measure the length of the value in cell**C5**. The function will return 3 as the value in**C5**is a**3 digits number**.**5-LEN(C5)**will be**2**.**The REPT function**will then repeat the number**zero**(**0**)**2 times**.- The
**ampersand**(**&**) sign will then**concatenate**the**value**of cell**C5**and the**two zero**(**0**) together to add**two trailing zeros**after the number in cell**C5**.

- Now, we will see that
**number**in cell**C5**has**two trailing zeros**.

- Then, we will drag the
**Fill handle**of cell**D5**to apply the formula to the rest of the cells.

- Finally, we will see that the formula has added
**two trailing zeros**Â to each number in the**Price**.

## Quick Notes

- You should have the
**Outlook**mail app to paste the range into the email body using VBA. - If you do not have a
**Developer**tab, you can make it visible in**File > Option > Customize Ribbon**. - To open the
**VBA**editor Press**ALT + F11.**You can press**ALT + F8**to bring up the Macro window.

**Download Practice Workbook**

## Conclusion

In this article, we have learned how to convert number to text keep trailing zeros in Excel. I hope from now on you can convert number to text keep trailing zeros in Excel easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!

## Related Articles

- How to Convert Number to Text in Excel with Apostrophe
- How to Convert Number to Text with Green Triangle in Excel
- Convert Number to Text without Scientific Notation in Excel
- How to Convert Number to Text with 2 Decimal Places in Excel
- How to Convert Number to Text for VLOOKUP in Excel

**<< Go Back to Excel Convert Number to Text | Learn Excel**