Thousands Separator with optional decimal

I'd like to format a worksheet with all of the numbers having a thousands separator but only displaying a decimal point if it's needed. Can this be done?

Yes, go to Format Cell, Custom and type in #,###.### (assuming a maximum of 3 digits, add more if you like). That should do it.

ChemistB

Sooooo close. That leaves me with a decimal point after every number, whether there's anything to put after it or not.

Hmmm, don't think you can get rid of the decimal point.

ChemistB

Initially fiddly but to get the exact desired result I would add another column next to the one you want to format, hide that real one and use a nested if statement instead..

eg: if the number 1,000,000 was in B10 (and I hid the B column) I would put in C10 the below formula:-

=(IF(B10>=1000,IF(B10>=1000000,"£"&ROUND(B10/1000000,2)&"M","£"&ROUND(B10/1000,2)&"k"),"£"&B10))

it would produce
1M

if B10 was 1,115,000 it would produce
1.12M
if B10 was 1,115 it would produce
1.12K
if B10 was 15,500 it would produce
15.5K

Change the round number to the desired decimal, I chose two here, but as you see it goes to 1 or none if there are zero's there.

You would have to add more nests for negative.