Discussions

Expand all | Collapse all

Validate and fix US Zip code (5) if needed

  • 1.  Validate and fix US Zip code (5) if needed

    Posted 10-03-2018 21:33
    I have a zip code field that may have more than or less than 5  characters.....I would like to create a text formula field that would Validate and fix my [Zip code (5)] field.

    If the zip code is related to a [State] that has a leading zero at the beginning --- then I would like to add a leading zero if missing.....

    Any advice or help is greatly appreciated




  • 2.  RE: Validate and fix US Zip code (5) if needed

    Posted 10-03-2018 21:37
    You can make a text formula field like this

    Right("0000" & totext([my numeric zip code field]),5)

    that will change a numeric field like

    1234
      to be 01234

    But I do not know what logic would be used to make a zip code shorter.  can you give an example of the data you are dealing with and if the field is text or numeric?


  • 3.  RE: Validate and fix US Zip code (5) if needed

    Posted 10-04-2018 12:27
    Mark, thank you!! for your help. I used your formula but in reverse order. (because many of the zip codes where Zip + 4 --- so I had to approach from the LEFT.

    Formula I used
    If([ZipZeroState]=true and Begins([ShipTo_Zip],"0"), Left([ShipTo_Zip],5),If([ZipZeroState]=true, Left("0" & [ShipTo_Zip],5),Left([ShipTo_Zip],5))


    [ZipZeroState] Case([ShipTo_State],"CT",true,"NJ",true,"MA",true,"ME",true,"NH",true,"PR",true,"RI",true,"VT",true,"AE",true,false)


    Thank you again


  • 4.  RE: Validate and fix US Zip code (5) if needed

    Posted 10-04-2018 12:56
    OK, glad my clues helped.


  • 5.  RE: Validate and fix US Zip code (5) if needed

    Posted 10-04-2018 14:23
    Always....thanks again