Validate zip codes

[Lostguy]

Hello!

I am trying to find a good formula that will validate zip codes, but
isnumber(), len(), etc. are not working for me.

Here are my testers:

00501
12h78
h1234
1234h
12345

It needs to allow only digits, including possible leading zeroes like
the first example. It can't allow letters in any of the positions.

If there is already a formula that validates 5 and 9 digit zip codes,
that would be ideal, so that the user could put the zip in as "xxxxx"
or "xxxxx-xxxx".

Oh, and it can't be VBA.

I appreciate the help!


VR/

Lost

 

[T. Valko]

This isn't very pretty but it seems to work.

**THE CELL MUST BE FORMATTED AS TEXT**

=OR(AND(LEN(A1)=5,COUNT(-MID(A1,ROW(INDIRECT("1:10")),1))=5),AND(LEN(A1)=10,MID(A1,6,1)="-",COUNT(-MID(A1,ROW(INDIRECT("1:10")),1))=9))

 

[T. Valko]

I forgot to mention that it works for either the standard 5 digit zip code
or the zip+4 format.

Note that an entry of all zeros (in either format) is accepted.

 

[ShaneDevenshire]

Hi,

This formula seems to work in Data Validation:

=AND(VALUE(REPLACE(C1,6,1,"")),OR(LEN(C1)=5,LEN(C1)=10))

If this helps, please click the Yes button

 

[T. Valko]

Accepts entries like:

1.222
1234567890
-123456789
1234E1

 

[Lostguy]

Biff,

In a new worksheet with your formula in cell B1 and with "12345" in A1
and A1 formatted as text:

(trying it out before I put it in Validation>Custom)

It is "true" for the first part of the AND (length=5), but for the
second part =COUNT(-MID(A1,ROW(INDIRECT("1:10")),1))=5, it is saying
"volatile" and "false".

So overall it is false, but 12345 should be valid.

I am using Excel 2003 if that makes a difference. I am not real smart
when it comes to the indirect function, so that could be the problem.

??

VR/

Lost

 

[T. Valko]

I thought you wanted to use data validation from the menu Data>Validation.
If you use the formula in a worksheet cell it has to be array entered.

Array formulas are entered
differently than a regular formula. After you type in a regular formula you
hit the ENTER key. With an array formula you *must* use a combination of
keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is,
hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formulas in squiggly brackets { }.
You
can't just type these brackets in, you *must* use the key combo. Also,
anytime you edit an array formula it *must* be re-entered as an array using
the key combo.

When you go to setup the data validation just copy/paste the formula into
the refedit (that little box on the data validation user form). It does not
need to be array entered in the refedit. Formulas entered in refedits are
automatically processed as arrays.

I can put together a small sample file that demonstrates this if you'd like.

 

[Lostguy]

Biff,

As you already know, the formula works perfectly when I stuck it in
the right place (it allows 5 or 9 digit zip codes, checks for correct
number of digits, and won't let letters slip through if typos are
made. It even works with leading zeroes!)

And thanks for that info about array formulas! Good stuff!

I appreciate all your help!

VR/

Lost

 

[T. Valko]

Good deal. Thanks for the feedback!