Data Validation Limits

[Guest]

I used Data validation to custom zip code because I needed
to limit the numbers. Some numbers started with 00 and if
I used the number or text choice the validation did not
work. Two questions:

Is there any way to limit the length of the number to 5,
similar to limiting text length?

Can I attach an error message if a number that is typed in
the cell isn't a number that is in my validation list, if
so how?

Thanks in advance.

 

[Frank Kabel]

Hi
for the first question try the following:
- preformat the cells as 'Text'
- limit the length of these cells.
As you have Zip-Codes you probably don't need to calculate with them.
So store them as 'Text' entries

Second question:
- if your validation list has the name 'validate' (goto 'Insert -
Name - Define' to create such a name) and you use the cell B1 enter the
following formula for data validation:
=COUNTIF(validate,B1)>0

 

[Peo Sjoblom]

Your question is a bit confusing, if you have a list of all the zip codes
then
you don't need anything else, everything that is not on that list will
return a message if you set
it up that way (look at the tabs input message and error alert)
Otherwise you can use custom and

=LEN(B1)=5

or

=LEN(B1)<=5

depending on what you mean by limit (up to 5 characters or less than or
equal to 5)

however if the zips are for the US a zip with a leading zero will return an
error message,
so I suggest you pre format (under format>cell>numbers) input cell to text
so you can use leading zeros..