how can I use preceeding zeros and data validation in Excel?

[Tropical]

I am trying to do 2 things. (1) use preceeding zeros in a 3 digit format,
which I can do in either text or numeric format. (2) I want to use data
validation of 3 digits, which seems to be my downfall. e.g. If I enter 036
or 008 in a cell, I want it to show as 036 or 008 and pass validation.

I can achieve this by changing to text format, but wonder if anyone knows
how to do it in numeric format. Thanks

 

[Bob Umlas]

If it's already formatted as 000, then you can use Decimal value < 1000, no?

 

[Billy Liddel]

Hi, select the cell(s), choose Data, Validation. Select whole number, Min 1,
MAx 999 and click OK

with the cells still selected choose Format, Cells, Number, Custom. in the
custom box type 000 and click OK

Peter

 

[Gord Dibben]

Numeric Formatting changes the appearance but not the underlying value.

So.........your entering as text is the answer.


Gord Dibben MS Excel MVP