Stop concatenate adding decimal places back

[ethanJCB]

Excel experts, hi!

How do I stop concatenate adding decimal places back?

Concatenate is referencing a simple cell containing a number formulated elsewhere and adding ":1" to the end (to give a ratio reading)

=CONCATENATE(AF32,":1")

The referenced cell AF32 is set to 0 decimal places, the cell containing the concatenate is set to 0 decimal places. Without concatenate, AF32 references (in any cell) as it should with no decimals, so why does concatenate add the rogue decimal places back in??? Excel tsk!!!! tsk!!!!!

[ethanJCB]

Fixed it ... :|

=CONCATENATE(TRUNC(AF32,0),":1")

I'm still keen to know why concatenate ignores rules & adds unwanted decimals back in though.

[AliGW]

This maybe?

CONCATENATE(ROUND(AF32,0),":1")

[ethanJCB]

Thanks Ali , both solutions work.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[klica]

Hi, tested both and concluded that
Option with "round" works better.
Especially if you want to show two decimal digits.
Thank you AliGW