How can I add decimals to a concatenate formula?

AlexBC · June 15, 2020, 4:02pm

I have a concatenate formula to display A1: 12345 and B1: 645.60, the problem is that it shows in C1: 12345 (654.6)
I need C1 to display 12345 (645.60) I need that zero to be displayed too.

Here is the formula I have: C1=CONCATENATE(A1," “,”(",B1,")")

Thanks for any help you can give!!!

anon73440385 · June 15, 2020, 4:14pm

Hello,

use: =CONCATENATE(A1;" ";"(";TEXT(B1;"0.00");")")

Hope that helps.

erAck · June 15, 2020, 4:53pm

Just be aware that if loaded in another locale that uses a different decimal separator the format would fail.

anon73440385 · June 15, 2020, 5:04pm

It won’t give a . (dot) - that’s true but the different decimal separator

AlexBC · June 15, 2020, 5:39pm

It worked! Thanks man!

erAck · June 15, 2020, 9:36pm

different decimal separator

It depends… that’s true if an en-US format with . dot decimal separator was used like 0.00 but if formatted with , comma decimal separator as 0,00 because that’s what the user is used to then when loading and recalculating in a locale where comma is group separator the value 12345 would be displayed as 12,345

Similar, using 0.000 (intended as three decimals) in a locale where . dot is the group separator the display string would be 12.345

Just pointing out TEXT() function traps most users are not aware of.