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

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.


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.

Convert Number to Text Keep Trailing Zeros in Excel


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.

Convert Number to Text and Keep Trailing Zeros in Excel Using the TEXT Function

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

Convert Number to Text and Keep Trailing Zeros in Excel Using the TEXT Function

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

Convert Number to Text and Keep Trailing Zeros in Excel Using the TEXT Function

Step 2:

  • Then, we will write the following formula in cell D5.
=TEXT(C5,"##0.000")

 TEXT Function

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.

Convert Number to Text and Keep Trailing Zeros in Excel Using the TEXT Function

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

drag the fill handle

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

Convert Number to Text and Keep Trailing Zeros in Excel Using the TEXT Function

Step 3:

  • Now, we will write down the below formula in cell E5.
=B5&" Price: "&D5

Convert Number to Text and Keep Trailing Zeros in Excel Using the TEXT Function

Formula Breakdown:

Ampersand sign (&) will concatenate the texts in cells B5 and D5 with the textPrice between the two texts.

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

Convert Number to Text and Keep Trailing Zeros in Excel Using the TEXT Function

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

drag the fill handle

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

Convert Number to Text and Keep Trailing Zeros in Excel Using the TEXT Function

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

Use the IF Function

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.

Use the IF Function to Convert Number to Text and Keep Trailing Zeros in Excel

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

drag the fill handle

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

Use the IF Function

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

Use the IF Function to Convert Number to Text and Keep Trailing Zeros in Excel

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


Similar Readings:


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.

Convert Number to Text and Keep Trailing Zeros in Excel Using the Apostrophe Symbol

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.

Use a VBA Code to Convert Number to Text and Keep Trailing Zeros in Excel

  • Now, click on the Insert button and select Module.

Use a VBA Code to Convert Number to Text and Keep Trailing Zeros in Excel

  • 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 ().

Use a VBA Code to Convert Number to Text and Keep Trailing Zeros in Excel

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

Use a VBA Code to Convert Number to Text and Keep Trailing Zeros in Excel

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.

Use a VBA Code to Convert Number to Text and Keep Trailing Zeros in Excel

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

Use a VBA Code to Convert Number to Text and Keep Trailing Zeros in Excel

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

Add Trailing Zeros Using Formula in Excel

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.

Add Trailing Zeros Using Formula in Excel

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

drag the fill handle

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

Formula in Excel


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


Related Articles

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo