Forum Discussion

Claude2024's avatar
Claude2024
Copper Contributor
Mar 19, 2024

How to apply a custom number format to a blank cell

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.

  • 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.
    • Claude2024's avatar
      Claude2024
      Copper Contributor
      Dear Steve,

      How do I set then "*** Please select ***" by default without having to manually place an intervention?
      • Steve_SumProductCom's avatar
        Steve_SumProductCom
        Iron Contributor
        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".

Share