cell settings

J

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
 
J

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
 
D

David Biddulph

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

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
 
G

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
 
J

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.
 
J

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.
 
Top