custom format for cells with nothing in them without using conditional formatting

Question

if a cell is blank I would like to add text using a custom format. I've seen where text is added if a value is above or below a certain value. Can you add text to the cell if the cell is blank? I've tried the following with no luck

[=0]"Enter value"* 0;

[=""]"Enter value"* 0;

Answer 1

Well, it can be done, and it can't. You can make it look like the format you require, as in the picture below. Cell C3 and Cell C5 are formatted identically, with a value in C5:

When you enter the text into the cell, in this case C5 the text disappears. The secret is in the one pixel column, Column B. B3 contains "Enter value" and is formatted red, bold. It's a cheat, but I have used it to good effect.

Answer 2

Select your cells and go to the number format dialog box and select Custom from the list on the left. Then in the Type text box, enter:

#,##0.00;-#,##0.00;"Enter value"

The entry before the first semi-colon specifies the format for positive numbers, the entry between the first and second semi-colons specifies the format for negative numbers. Change these to the correct format for your own data. The third entry specifies the format for zero.

If you format your cells this way and type 0 into the cell, the cell will CONTAIN zero, but will display whatever text you specify.

This will only work if the cell contains 0. I could not think of any way to do this for a text entry without some kind of "helper" function in another cell as jmaglasang suggests, or without some VBA code on the Worksheet_Change event.

If you would like a VBA solution, let me know. However, this means that users would have to have macros enabled for that to work.

Answer 3

Just to add to the answer provided by @dendarii -- you can add a fourth, for text. For example, with this number formatting:

"My Positive Number";"My Negative Number";"My Zero";"My Text"

...the cell will display "My Text" if any non-numeric text is entered.

However, unfortunately there isn't a fifth option for blank cell.

So if your scenario allows having, say, a space character in the cell by default, then you could do it with that fourth option. But if the user deletes that space character from the cell, leaving it blank, your custom display text goes away.

Answer 4

If I have understood this you can try:

IF(condition,TrueValue,FalseValue)

Empty value are considered false when being evaluated.