Forum Home
Press F1
Thread ID: 66374	2006-02-20 09:59:00	Excel cell formatting question	Tony (4941)	Press F1

Post ID	Timestamp	Content	User
432433	2006-02-20 09:59:00	This has got to be another of those "dead easy when you know how to do it" questions: What number format do I apply to a cell so that zero values show blank, and anything else shows 2 decimal places? E.G.: 12345.67 displays as 12345.67 0.01 displays as 0.01 0.1 displays as 0.10 0 displays as blank. I can get it so zero displays with just the decimal point (e.g. " . "), but I that's not what I want. :badpc:	Tony (4941)
432434	2006-02-20 10:20:00	Use the "," button on the formatting toolbar, it displays as you ask (accounting format) but a "0" displays as "-"	godfather (25)
432435	2006-02-20 10:22:00	And once done with the " , " button, select the same cells and choose Format cells - number - custom. Edit the " - " in the string to " " (the quotes are shown near the right on the edit window under formatting, that replaces the " - " with a space or a null.	godfather (25)
432436	2006-02-20 19:14:00	And once done with the " , " button, select the same cells and choose Format cells - number - custom . Edit the " - " in the string to " " (the quotes are shown near the right on the edit window under formatting, that replaces the " - " with a space or a null . Got the previous post OK, don't understand this one . In Excel 2003, if I do Format|Cells|Custom, I get the Format cells dialog with the number tab, with a long format string selected and - shown as the sample . I'm not sure what you mean by " edit the string " - do you mean the selected format string? (the quotes are shown near the right on the edit window under formatting . . . This is the bit I don't get . What edit window? I don't see anything labelled " formatting " :confused: :confused: :confused:	Tony (4941)
432437	2006-02-20 19:22:00	OK, in the "Number" tab window, there is only one place you can edit the format, in the "Type" box. The full string you should have (only part of it is visible) will be: _-* #,##0.00_-;-* #,##0.00_-;_-* "-"??_-;_-@_- Edit the bit "-" as discussed above. It should then look like: _-* #,##0.00_-;-* #,##0.00_-;_-* " "??_-;_-@_- I did not mean a "formatting" window so named, just referring to the number format string, being the only place its actually or physically possible to edit anything in that window.	godfather (25)
432438	2006-02-20 19:27:00	Have a look at this - support.microsoft.com	andy (473)
432439	2006-02-20 19:55:00	Thanks GF, got it now. After I posted I thought about it a bit more and realized that it must be as you said. I was coming back to the PC to try again and lo! you had beaten me to it. Thanks very much. :thumbs:	Tony (4941)
432440	2006-02-20 19:59:00	I've been coping with it for years with IF(X=0,"",X), where X is the result of your calculation as I was couldn't find a better way. I'll try these other suggestions with great interest.	Mike S (1766)
432441	2006-02-20 22:20:00	Your way does work Godfather, but if negative numbers are involved then the minus sign is now left adjusted in the cell rather than directly in front of the number. I find minus signs easy to overlook when they appear ‘separately’. The left adjustment happens as soon as one clicks on the “,” button on the number format toolbar. My way is laborious but does keep the “-“ where I expect to see it. I should add that when dabbling in the list of custom options for the formatting I just followed your instructions slavishly without any real understanding. The actual logic of it isn’t immediately obvious.	Mike S (1766)
432442	2006-02-20 23:20:00	The general case of what GF was indicating is that the formatting contains several elements (which was what I had forgotten) - format for positive numbers, format for negative numbers, format for zero values and format for text values. The format elements are separated by ";". Try this format: "POS";"NEG";"ZERO";"TEXT" You should get these results: 10 gives POS -10 gives NEG 0 gives ZERO AAA gives TEXT What GF was telling me was to take an existing format and replace the third element ("ZERO" above) with "", which will give a blank for zero values. The other formatting elements can be whatever you want. GF - have I got that right? HTH	Tony (4941)
1 2