Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

zip code validation

Posted on 2011-05-01
5
Medium Priority
?
229 Views
Last Modified: 2012-05-11
In a cell, I have zip+4 format, but this does not  prevent erroneous entries.  I need code to insure 5 numbers, hyphen, four numbers, ie #####-####.  Other charfacters and too many numbers or not enough numbers should bring up an error with a retry or cancel.   Would the zip+4 formatting still be required?   Please be thorough in your answer.  Thanks in advance for your kind help!  rgds/ron
0
Comment
Question by:1r3o2n8
  • 3
  • 2
5 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35501344
Use this validation rule:

=AND(SEARCH("?????-????",A1)=1,LEN(A1)=10,ISNUMBER(VALUE(SUBSTITUTE(A1,"-",""))))

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35501352
To use a validation rule, select the cell to be validated, navigate to the Data tab, click Data Validation to bring up the Data Validation dialog. Select Custom and enter the formula above changing A1 to the selected cell. Click OK.

Kevin
0
 

Author Comment

by:1r3o2n8
ID: 35501722
It doesn't seem to work for me.  Go to the area of J20 in sht Customer1 and try it for me.  THX
RETAXES---Copy1-040211sendOnly.xlsm
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 35501948
Fixed. When entering the formula you have to change the cell reference to the selected cell.

Kevin
RETAXES---Copy1-040211sendOnly.xlsm
0
 

Author Closing Comment

by:1r3o2n8
ID: 35502038
You're wonderful!  Thanks alot.  rgds/ron
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question