How to apply a custom number format to a blank cell

Copper Contributor

I would like to know how I can apply a custom number format to a blank cell.   If a cell is blank, then display the value "*** Please select ***".   The cell has a drop down data validation list that by default shows an empty choice.  I am not interested in VBA code.

3 Replies
If the cell is blank, then you can't show text in it. You can create a number format like 0;0;0;"*** Please select *** "@, where the 4th part controls what happens when text has been entered into the cell. However, it will show nothing when there is no value in the cell.

I think the best way to do this is to add "*** Please select ***" as the first choice in your drop-down and set the cell value to that by default.
Dear Steve,

How do I set then "*** Please select ***" by default without having to manually place an intervention?
You will need to create the data validation rule to include "*** Please select ***" as a choice in your drop-down list, and then choose "*** Please select ***" in that cell. You actually don't need to have it as one of the choices in your list, but I think it makes it less likely that you'll have an error.

To avoid including "*** Please select ***" as a choice in your drop-down list, you can go to the "error alert" tab, and uncheck the option for "Show error alert after invalid data is entered". Then you can simply type "*** Please select ***" into the cell. However, this will allow any value to be placed in that cell, even if it's not on the drop-down.

I'm not clear on what you mean by "manually place an intervention".