Data Validation

  • Thread starter Thread starter iamnu
  • Start date Start date
I

iamnu

I want to use data validation on a cell where the user MUST enter any
four digits, leading zeros are permitted.

Can someone explain how I do this?
 
You don't say if 0001 and 1 are to be considered equivalent, so I'll give this a shot.
Data - Validation - Whole number - Between 0 and 9999.
 
You don't say if 0001 and 1 are to be considered equivalent, so I'll give this a shot.
Data - Validation - Whole number - Between 0 and 9999.
Click to expand...


"The user MUST enter 4 digits." That means that 0001 and 1 are NOT
equivalent.
Thanks for trying to help, though. I appreciate any suggestions...
 
Try this Custom Validation formula...

=AND(LEN(A1)=4,ISNUMBER(A1))

Rick
 
If the value is a number, then excel will see 0001 and 1 as equal.

But why impose the way the user enters the data?

Just use a custom numberformat of 0000
and use that data validation of between 1 and 9999.
 
That didn't work for me.

Excel stripped off the leading 0's before applying the validation rules. And so
the =len(a1) portion failed.
 
Try this Custom Validation formula...

=AND(LEN(A1)=4,ISNUMBER(A1))

Rick
Click to expand...

Thanks Rick, but that doesn't work either. Your formula accepts any 4
digit number UNLESS it begins with a zero.
I want to be able to enter 0123, for example. Do you have another
suggestion?
 
Try this instead... Format the cells as Text and then use this Validation
formula...

=AND(ISNUMBER(VALUE(C1)),LEN(C1)=4)

Have you given Dave's idea any consideration? I think that sounds like a
better idea to me than forcing the user to type leading zeroes.

Rick
 
Yeah, it doesn't work for me either.<g>

The OP mentioned leading zeroes in his post, so what makes you think I would
have tested for that particular condition.<bg>

Rick
 
Yeah, it doesn't work for me either.<g>

The OP mentioned leading zeroes in his post, so what makes you think I would
have tested for that particular condition.<bg>

Rick
Click to expand...

The new formula didn't work either, but I am not considering using
Dave's suggestion. Thanks all for the help.
 
The new formula didn't work either,

Did you remember to Format the cells as Text?
but I am not considering using
Dave's suggestion. Thanks all for the help.
Click to expand...

Did you mean to include the word "not" in your statement? The way the last
sentence is worded, it sounds like you do not need any further help, but the
statements that precede the last sentence makes it sound like you still do.

Rick
 
Back
Top