Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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;
share|improve this question

migrated from stackoverflow.com Nov 2 '10 at 11:35

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer
1  
That's really clever. Excellent workaround. –  guitarthrower Nov 7 '09 at 23:05
    
+1 for a really clever trick! –  Robert Koritnik Aug 28 '10 at 21:49

enter image description here

If I have understood this you can try:

IF(condition,TrueValue,FalseValue)

Empty value are considered false when being evaluated.

share|improve this answer
    
thanks, but I would want the text to appear without any formulas in the column B that you have in your image. –  guitarthrower Oct 21 '09 at 6:15

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.

share|improve this answer
    
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 Oct 21 '09 at 20:11
    
I should add, that I'm trying to keep VBA out of it to ease use and distribution. –  guitarthrower Oct 21 '09 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 Oct 22 '09 at 8:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.