# Concatenate with text() to format as currency truncates trailing zero

#### Me_In_Va

##### New Member
I am trying to get a formula to display a currency total using the text() function and the concatenate() function but it drops the trailing zero. Here is the formula that I am using now:

=IF(H14>I14,CONCATENATE("Over by: ",TEXT(H14-I14,"\$#,###.##")),CONCATENATE(TEXT(I14-H14,"\$#,###.##")," was not spent")))

The formula is comparing two figures:
Col H: Total spent
Col I: Budget toal

If total spent is greater than the budget total then the first condition will display "\$xxx.xx over". If the total spent was less than budget then it should display "\$xxx.xx was not spent". However, it is displaying "\$100.2 not spent" instead of "\$100.20 not spent" if the cents has a zero in the pennies place.

Any help on getting this to work the way I would like without the use of VBA would be greatly appreciated!

Thanks,
Olli

Last edited:

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Joe4

##### MrExcel MVP, Junior Admin
You have the wrong format selected. ".##" will drop trailing zeroes in a decimal. Choose ".00" instead.

Try:
Code:
``[COLOR=#333333]=IF(H14>I14,CONCATENATE("Over by: ",TEXT(H14-I14,"\$#,###.[/COLOR][COLOR=#ff0000]00[/COLOR][COLOR=#333333]")),CONCATENATE(TEXT(I14-H14,"\$#,###.[/COLOR][COLOR=#FF0000]00[/COLOR][COLOR=#333333]")," was not spent")))[/COLOR]``

Last edited:

#### Me_In_Va

##### New Member
Thank you! Worked like a charm!

You have the wrong format selected. ".##" will drop trailing zeroes in a decimal. Choose ".00" instead.

Try:
Code:
``[COLOR=#333333]=IF(H14>I14,CONCATENATE("Over by: ",TEXT(H14-I14,"\$#,###.[/COLOR][COLOR=#ff0000]00[/COLOR][COLOR=#333333]")),CONCATENATE(TEXT(I14-H14,"\$#,###.[/COLOR][COLOR=#FF0000]00[/COLOR][COLOR=#333333]")," was not spent")))[/COLOR]``

#### Scott Huish

##### MrExcel MVP
A shorter formula:

=TEXT(H14-I14,"""Over by: ""\$#,###.00;\$#,###.00 "" was not spent"";""\$.00 was not spent""")

#### Rick Rothstein

##### MrExcel MVP
A shorter formula:

=TEXT(H14-I14,"""Over by: ""\$#,###.00;\$#,###.00 "" was not spent"";""\$.00 was not spent""")
Maybe the OP would like to take advantage of the H14=I14 condition and tailor the message for it...

=TEXT(H14-I14,"""Over by: ""\$#,###.00;\$#,###.00 "" was not spent"";""Exact amount was spent""")

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,021
Messages
5,856,849
Members
431,837
Latest member
megantang

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back