Zip Code Validation - length =5 or 9

[Daron]

I am setting up a sheet 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.

any help much appreciated!

- Daron

 

[Guest]

Set the Allow Field to "Custom", then enter this formula:

=OR(LEN(B3)=5,LEN(B3)=9)

Or, if you want to also insure that the user inputs a whole number:

=AND(B3=INT(B3),OR(LEN(B3)=5,LEN(B3)=9))

HTH,
Elkar

 

[Peo Sjoblom]

Do you have a typo? You are referring to 2 different cells?


--


Regards,


Peo Sjoblom

 

[Rick Rothstein \(MVP - VB\)]

I am setting up a sheet 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

 

[T. Valko]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Daron]

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.

 

[Rick Rothstein \(MVP - VB\)]

Try setting your Validation to "Custom", not Whole Number, for you data
range... use your same formula though.

Rick

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.

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

 

[Daron]

Rick,

That worked! Thanks for your help!

-Daron

Try setting your Validation to "Custom", not Whole Number, for you data
range... use your same formula though.

Rick

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.

 

[T. Valko]

Just because I'm anal....

You might want to include a test that the entry is numeric otherwise it'll
accept any entry that meets the length conditions.

=AND(ISNUMBER(B4),OR(LEN(B4)=5,LEN(B4)=9))

--
Biff
Microsoft Excel MVP

Rick,

That worked! Thanks for your help!

-Daron

Try setting your Validation to "Custom", not Whole Number, for you data
range... use your same formula though.

Rick

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\)"
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.



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.

in
messageI am setting up a sheet 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...

Rick

 

[Rick Rothstein \(MVP - VB\)]

I don't think you are being "anal" at all... you raise an excellent point.
Daron's original control for this was an attempt to use a Whole Number
validation (which, of course, didn't work when coupled with the attempted
length controls). Once I switched Daron over to the "Custom" validation, it
exposed his entries to non-digits. I'm just angry at myself for not
recognizing this in my previous answer. Good follow up!

Rick

Just because I'm anal....

You might want to include a test that the entry is numeric otherwise it'll
accept any entry that meets the length conditions.

=AND(ISNUMBER(B4),OR(LEN(B4)=5,LEN(B4)=9))

--
Biff
Microsoft Excel MVP

Rick,

That worked! Thanks for your help!

-Daron

Try setting your Validation to "Custom", not Whole Number, for you data
range... use your same formula though.

Rick



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\)"
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



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)" <[email protected]>
wrote in
messageI am setting up a sheet 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

 

[Daron]

That formula worked a bit better.

When formatted with
[>99999]00000-0000;00000
I now have the perfect solution!

Thanks to all who replied!

Just because I'm anal....

You might want to include a test that the entry is numeric otherwise it'll
accept any entry that meets the length conditions.

=AND(ISNUMBER(B4),OR(LEN(B4)=5,LEN(B4)=9))

--
Biff
Microsoft Excel MVP

Rick,

That worked! Thanks for your help!

-Daron

Try setting your Validation to "Custom", not Whole Number, for you data
range... use your same formula though.
Rick

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\)"
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

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
in
messageI am setting up a sheet 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