How to format empty cells to display a zero

[Tom Urtis]

OK, I'm having a brain block regarding cell formatting, and (believe it or not) I'm trying to avoid using VBA if possible to accomplish the kind of formatting I need. I want to have empty cells display a zero, and I'm probably forgetting something simple about how to do it.

Background:

I have a range of empty cells in A1:M20. "Empty cells" means no formulas, values, or formatting of any kind...cells that are in untouched condition just as when you start a new workbook.

I am creating a budget workbook model, where cells in A1:M20 might have whole numbers manually entered into them. No text or decimal places are involved.

Not every cell will end up having a number manually entered into it. When it does, the range shall be formatted for comma separators, such as you'd get with the #,##0;-#,##0 custom format. No problem so far.

The question I have is, when no number otherwise exists in a cell (either because the cell has never been touched, or its previous number was deleted), what would be the custom format to display a zero ("0") in that cell? The client wants to see every cell contain either whole numbers (formatted for comma separators), or a zero.

I could put code in the worksheet module to display a zero in cells with a zero-length string, but is there a native custom format to achieve this?

[Ponsonby]

Cell formatting displays the contents of the cell in the desrired format - I don't think you can use cell formatting to display something when there is nothing there.

[PaddyD]

Hi Tom,

is there a native custom format to achieve this

Not that I can think of (which may or may not mean anything). As formats apply to data values, I guess you're going to have problems coercing native formatting functions to recognise the absence of data as a data value!

Any reason for the non-VA preference?

paddy

[Tom Urtis]

Thanks guys; you are probably correct...I know there are native custom formats to put a dash in a cell with nothing in it, but I thought maybe I'm missing something obvious, with a zero instead, which doesn't work when trying to modify _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_).

To answer your question Paddy - - I'm trying to explore VBA alternatives just for my own knowledge, because I wonder if sometimes I use VBA too quickly for solutions. Maybe this is a case where VBA really is necessary. No big deal to me if so, but it seems there are native non-VBA solutions for lots of problems, so I thought I'd take a shot and see if this can be done without code.

[Ponsonby]

Tom

The format you posted _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_) will not display a dash in a cell that is empty.

It custom formats zero values (the third part of the format) so that a dash will be displayed when the cell's value is zero.

[Tom Urtis]

Yes I know, that was just an attempt to convey the comparison example that if a cell has no formula, what format could display a zero, sort of like how _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_) displays a dash with a zero-returned formula.

[ekareem]

Hi,
I was wondering how to get a dash displayed when the cell is empty using a format similar to the one in this thread?

That is, using cell custom format,
if cell value is 0 I want -
if cell value is empty I want -

How can I do that please?