Re: Zip Code Validation - length =5 or 9

Thanks everyone for your suggestions. It would appear that I need to
be a bit more specific.

The current format for these cells is [>99999]00000-0000;00000 so that
the users don't have to worry about the dash

I'm not worried about leading 0's as all my users are in NY State in
locations where the zip code should start with a 1.

And, I had an error in my original formula. The correct formula I was
trying to use was:
=OR(LEN(B4)=5,LEN(B4)=9)
With the Validations set to Allow Whole Number

The problem I am having is that regardless of the number of digits
involved, I receive the error alert.

If I am missing anything, please let me know.


On Oct 30, 8:43 pm, "Rick Rothstein \(MVP - VB\)"
<rickNOSPAMn...@xxxxxxxxxxxxxxxxx> wrote:
I ruled out that we were talking about Custom/Special/ZipCode+4 format
because the OP asked about checking the length equal 4... a 5-digit entry
would appear with 4 leading zeroes and that seemed like an unlikely default
for a 5-digit zip code. I sort of ruled out that we were talking about
Custom/Special/ZipCode formatting because 12345 would enter as a number
(right justified) whereas 12345-6789 would enter as text (left justified).
Remember, I started my response by asking if a dash could be present and
then predicated my answer on it being allowed. Anyway, with those conditions
ruled out, I figured that to have a consistent display (all justified the
same way), the OP almost had to be formatting his column as Text. Of course
I could be wrong, but that was my thinking at the time.

Rick

"T. Valko" <biffinp...@xxxxxxxxxxx> wrote in message

news:%23l8x08zGIHA.5208@xxxxxxxxxxxxxxxxxxxxxxx

This all depends on how the zip codes are entered.

Are they formatted as TEXT to allow for leading 0s? Are they formatted
using one of the existing zip code formats? Are they formatted using a
custom number format?

If they're formatted as zip code or a custom number then your formula
fails when having to deal with leading 0s.

--
Biff
Microsoft Excel MVP

"Rick Rothstein (MVP - VB)" <rickNOSPAMn...@xxxxxxxxxxxxxxxxx> wrote in
messagenews:u0pgWbzGIHA.1316@xxxxxxxxxxxxxxxxxxxxxxx
I am setting up a *** to that will include zip codes. How do I set
the validation to only allow a 5 digit or 9 digit value?

I've tried a Whole number with:
=OR(LEN(B3)=5,LEN(B4)=9)
but this doesn't allow anything to be entered. I've been searching
thru the group for 2 days, and can't find what I need.

Do your "9-digit" zip codes allow for the dash between characters 5 and
6? If I did everything right, this formula will allow you to use a dash
or not, make sure the "shape" is right (#####, ######### or #####-####)
and that only numbers (along with a single dash) can be entered...

=AND(OR(LEN(A1)=5,LEN(SUBSTITUTE(A1,"-",""))=9),ISNUMBER(VALUE(SUBSTITUTE(A1,"-",""))),(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))<=1),IF(ISNUMBER(FIND("-",A1)),IF(FIND("-",A1)=6,TRUE),TRUE))

Rick


.