+ Reply to Thread
Results 1 to 4 of 4

Data validation for both 5 and 9-digit zip codes with hyphen for 9-digit zip codes

  1. #1
    Registered User
    Join Date
    08-12-2014
    Location
    Fremont
    MS-Off Ver
    2013
    Posts
    19

    Data validation for both 5 and 9-digit zip codes with hyphen for 9-digit zip codes

    I would like to combine the following Excel formulas in the custom data validation field:

    =IF(AND(ISNUMBER($B$55)),OR($AH$55=5,$AH$55=9),IF('Sheet1'!$D$5=0,FALSE,IF('Sheet2'!$B$6="Incomplete",FALSE,IF($AF$263<9,FALSE,TRUE))))

    And if a 9-digit zip code is typed in, I would like a hyphen to appear between the first 5 digits and the last 4 digits.

    I've considered combining this formula with the aforementioned IF function, but I'm not sure how that would work:
    =REPLACE($B$55,6,0,"-")

    Or, placing the aforementioned REPLACE function cell in AJ55 and have something like the following:
    =IF(LEN($B$55)=9,$AJ$55))

    Thank you in advance!

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Data validation for both 5 and 9-digit zip codes with hyphen for 9-digit zip codes

    Would an adaptation of something like this in B1 and copied down work?

    Row\Col
    A
    B
    C
    D
    1
    61873-9717 61873-9717 In b1 =IF(LEN(A1)>5,LEFT(A1,5)&"-"&RIGHT(A1,4),A1)
    2
    44560
    44560
    3
    618739717
    61873-9717

  3. #3
    Registered User
    Join Date
    08-12-2014
    Location
    Fremont
    MS-Off Ver
    2013
    Posts
    19

    Re: Data validation for both 5 and 9-digit zip codes with hyphen for 9-digit zip codes

    Thanks for the response, FlameRetired. Going off of that, is there any way I can adapt that formula into a data validation IF function?

    I was thinking something to the effect of, if what is entered into the data validated cell meets the criteria, the cell will reference another cell that will show the hyphen for 9-digit zip codes.

    Is there anyway to do a custom data validation that can reference another cell if true?

    Thanks!

  4. #4
    Registered User
    Join Date
    08-12-2014
    Location
    Fremont
    MS-Off Ver
    2013
    Posts
    19

    Re: Data validation for both 5 and 9-digit zip codes with hyphen for 9-digit zip codes

    Never mind. I was able to get what I needed by using both custom data validation and conditional formatting.

    Thanks you again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Data validation list - country names are selections but 2 digit codes need to be result
    By Grilleman in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-07-2014, 11:28 AM
  2. [SOLVED] Addresses - truncate 11 digit zip codes and leave 5 digit zip codes
    By landisf in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-25-2013, 11:15 PM
  3. Working with 5-digit and 9-digit zip codes
    By WDP in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-14-2012, 10:54 AM
  4. Converting 9-digit zip codes to 5-digit zip codes
    By WDP in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-22-2009, 09:47 PM
  5. [SOLVED] Sorting by 5 digit & 5 digit plus 4 zip codes
    By D Marie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-22-2006, 12:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1