cell settings

[jaz]

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

 

[John C]

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

 

[jaz]

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

 

[Gord Dibben]

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 Allow>Text Length between 0 and 2

Enter 01 and OK

Enter 001 and not OK


Gord Dibben MS Excel MVP

 

[John C]

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]

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.