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 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.
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
Get FREE Advanced Excel Exercises with Solutions!
