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

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:

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.

• 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 “Fill 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 &.

•  After writing the formula and obtaining the result in cell E5, we drag the Fill handle icon to get the results of all the other cells.

• The final results will be like the following table:

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

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.

• 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 Fill 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.

• Now we drag the Fill 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

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.

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.

• We drag the Fill handle icon from the bottom left of cell D5 and get the following results summed up in a table.

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.

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 Fill 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