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

**Table of Contents**hide

**Download Practice Workbook**

Download this practice book to exercise the task while you are reading this article.

**4 Easy Ways to Convert Number to Text and Keep Trailing Zeros in Excel**

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 Numbers to Texts/Words 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**.

**Related Content:**** Excel VBA: Convert Number to Text with Format (A Total Guide)**

**Similar Readings:**

**How to Convert Number to Text for VLOOKUP in Excel (2 Ways)****Convert Number to Text without Scientific Notation in Excel****How to Convert Number to Text with 2 Decimal Places in Excel (5 Ways)**

**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 (3 Easy Methods)**

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

If you are familiar with the **VBA macro**, 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**.

**Read More:** **Excel VBA to Convert Number to Text (4 Examples)**

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

**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!!!