Retain Trailing Zeros in a Text String


The ExcelHelpDesk Support team received the following request for help

Problem – Retain Trailing Zeros in Text String

I am putting together some formulas for a publishing company that wants me to retain zeros after whole numbers involving money (for instance, $30.0 million). When I use the following formula, it cuts off all of the zeros following whole numbers. Is there a way I can configure the formula to retain zeros? I had one for $204.0 million, but four course it cut off the zero.

=CONCATENATE(“(2011): “,B6, ” units/$”,E6, ” mil”)

Thanks Peter !!

For this problem we received a sample workbook, that demonstrated the problem that needed to be solved. See the screen shot below of the spreadsheet and the formula that had been developed. You can see in the example that the trailing zero is being removed from the text string.

Step 1 – Include TEXT function to string

The solution to this problem is to include a TEXT function to the string and then to apply a specific format that will always show the trailing zero for the number that is to be display. So we can simply modify the existing function in the following way

We have the following formula
=CONCATENATE(“Commercial banks (2011): “, B22, “; deposits: $”,H22,
” bil. Savings institutions (2011): “,E22, “; deposits: $”, I22, ” mil.”)

That becomes
=CONCATENATE(“Commercial banks (2011): “, B22, “; deposits: $”,H22,
” bil. Savings institutions (2011): “,E22, “; deposits: $”, TEXT(I22,”0.0″), ” mil.”)

Once copied down the strings then include the trailing zero in all cases.

The key part of the change in the formula is the use of the TEXT function. In combination with the format value as an argument. In this case we have specified a single decimal place by using “0.0” if we wanted two (2) decimal places we would have used “0.00”.

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment