hello there

is there a way that a cell can be set to no more then 2 digits
eg, should accept 01,02 and not 001 or 002

many thanks
jez

Data|Validation
Settings:
Allow: Whole Number
I would select between 0 and 99 (or -99 and 99 if you allow negatives).

Then format the cell, custom as 00
--
John C


"jaz" wrote:

hello there

is there a way that a cell can be set to no more then 2 digits
eg, should accept 01,02 and not 001 or 002

many thanks
jez

But 001 and 0001 are between 0 and 99, so that won't do the trick, John.
--
David Biddulph

"John C" <johnc@stateofdenial wrote in message
...
Data|Validation
Settings:
Allow: Whole Number
I would select between 0 and 99 (or -99 and 99 if you allow negatives).

Then format the cell, custom as 00
--
John C


"jaz" wrote:

hello there

is there a way that a cell can be set to no more then 2 digits
eg, should accept 01,02 and not 001 or 002

many thanks
jez

thanks david and john
i did try that before but i want it to set so they cant even press space bar
and then 2 numbers
it must be some code in vb otherwise i dont think that can be done in excel
access is very easy because you can set the field size but in excel i have
not been able to find
any vb code?
cheers
jez

"David Biddulph" wrote:

But 001 and 0001 are between 0 and 99, so that won't do the trick, John.
--
David Biddulph

"John C" <johnc@stateofdenial wrote in message
...
Data|Validation
Settings:
Allow: Whole Number
I would select between 0 and 99 (or -99 and 99 if you allow negatives).

Then format the cell, custom as 00
--
John C


"jaz" wrote:

hello there

is there a way that a cell can be set to no more then 2 digits
eg, should accept 01,02 and not 001 or 002

many thanks
jez

01 is one digit according to Excel.

If you type 01 in a cell Excel will drop the 0

Pre- format the cells as Text

Then AllowText Length between 0 and 2

Enter 01 and OK

Enter 001 and not OK


Gord Dibben MS Excel MVP

On Thu, 9 Oct 2008 13:45:00 -0700, jaz
wrote:

hello there

is there a way that a cell can be set to no more then 2 digits
eg, should accept 01,02 and not 001 or 002

many thanks
jez

You are correct, which is why it is formatted as 00 instead of 000,
therefore, even if they enter 001 it will only show as 01.
--
John C


"David Biddulph" wrote:

But 001 and 0001 are between 0 and 99, so that won't do the trick, John.
--
David Biddulph

"John C" <johnc@stateofdenial wrote in message
...
Data|Validation
Settings:
Allow: Whole Number
I would select between 0 and 99 (or -99 and 99 if you allow negatives).

Then format the cell, custom as 00
--
John C


"jaz" wrote:

hello there

is there a way that a cell can be set to no more then 2 digits
eg, should accept 01,02 and not 001 or 002

many thanks
jez

And how are you limiting the entry to digits as the OP originally requested?
Text length means I could put ZZ for ZZ top if I wanted, and since my text
length is between 0 & 2, it would be a valid entry.
--
John C


"Gord Dibben" wrote:

01 is one digit according to Excel.

If you type 01 in a cell Excel will drop the 0

Pre- format the cells as Text

Then AllowText Length between 0 and 2

Enter 01 and OK

Enter 001 and not OK


Gord Dibben MS Excel MVP

On Thu, 9 Oct 2008 13:45:00 -0700, jaz
wrote:

hello there

is there a way that a cell can be set to no more then 2 digits
eg, should accept 01,02 and not 001 or 002

many thanks
jez