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

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
--
Tropical

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

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

"Tropical" wrote in message
...
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
--
Tropical

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

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

"Tropical" wrote:

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
--
Tropical

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

Numeric Formatting changes the appearance but not the underlying value.

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


Gord Dibben MS Excel MVP

On Wed, 21 Mar 2007 13:17:00 -0700, Tropical
wrote:

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