4

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;
Improve this question

4 Answers 4

Reset to default
9

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:

alt text

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.

Improve this answer
1
  • 1
    That's really clever. Excellent workaround.
    – guitarthrower
    Commented Nov 7, 2009 at 23:05
4

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.

Improve this answer
3
  • I suspected that it wasn't possible without some VBA trickery. Thanks for the explanation on the formatting. I've never really understood how that worked.
    – guitarthrower
    Commented Oct 21, 2009 at 20:11
  • I should add, that I'm trying to keep VBA out of it to ease use and distribution.
    – guitarthrower
    Commented Oct 21, 2009 at 20:11
  • 3
    True, VBA does complicate things in some environments. FYI there's a fourth possible section on custom number formats which specifies what to display if the cell contains text. However, I've never used that fourth option.
    – dendarii
    Commented Oct 22, 2009 at 8:19
1

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.

Improve this answer
-1

enter image description here

If I have understood this you can try:

IF(condition,TrueValue,FalseValue)

Empty value are considered false when being evaluated.

Improve this answer
1
  • thanks, but I would want the text to appear without any formulas in the column B that you have in your image.
    – guitarthrower
    Commented Oct 21, 2009 at 6:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .