Custom format to show decimals only when needed

Hey guys,

I have a sheet with some numbers that have different decimals, e.g.
5,90625
14,625
10,125
0,0525
0,5125
0
0,495
0,185

Now I want to format these cells so that the cells with a value (no zero) have 2 decimals, but zero still shows only as 0. When formatting to a number with two decimals I get 0,00 which I do not want. I have tried the custom format [>0]#,##;[=0]0 However, this changes 0,5125 to ,51 where I want it to show 0,51. The zeros do show as just 0 and other values get 2 decimals, so I'm close, I just cant figure out why the 0 dissappears. Does anyone have any thoughts?

Cheers

Hey Zbor,

Unfortunately, excel gives me the error 'cannot use the number format you typed' This might be due to comma, semi-column conversion for different excel versions, when I replace the commas with semicolumns (#.##;-#.##;0;@) excel exepts it but values between 0 and 1 are now blank, and there are no decimals. When I replace the periods with commas (#,##;-#,##;0;@) The result is the same as I previously had, the zero before the decimals is left out, rest is good.

Little update: [=0]0;0,## now gives me almost correct values, the zeros show, I now get 0,51 However, whole numbers now get a comma but no decimals (e.g. 14 shows as 14,) Anyone have any thoughts on how to fix this last little issue? Rest is working fine, even though I don't quite understand why, for example I thought this code would change 1,6875 to 0,69 (because the 0 is hardcoded in the code) Trial and error ftw I guess.

Edit: Just missed your reaction before posting this MrShorty, it explains the part I didnt understand

Thanks for your replies guys. MrShorty, too bad they haven't fixed this, seems like a perfect solution is not possible then. As my cells are already fairly complicated (nested VLOOKUPS and IFS etc.) Nesting them in TEXT() would make it a bit too complicated newdoverman, thanks for your input, I have altered your suggestion a bit to 0,#0;-0,#0;0 it now shows zero's only, max 2 decimals, and round numbers get one zero (e.g. 14 shows as 14,0). An accaptable solution for me, not as ugly as a lot of decimals and single comma's showing, thanks for your help.