4 Replies Latest reply on Nov 7, 2016 9:49 AM by philip.watkinson

    US zip code calculated field.

    Sanket Shah

      In one of my data file, i get U.S zip codes in various format.i.e.

      • (no leading zero) i.e. 1234
      • text after number i.e. 12345o
      • full zip code. i.e. 12345-1234

       

      i'm trying to convert this into proper 5 digit US zip code. i.e. 12345. or 01234 (if the zip is 4 digits)

       

      in excel, i would use this formula to get around it (assuming that zip codes is in cell A2)

      =LEFT(TEXT(A2,"00000"),5)

       

      how can i do this in tableau (v.8) as a calculated field? i could not find text function.

      any suggestions are greatly appreciated.

       

      thanks,

      .

        • 1. Re: US zip code calculated field.
          Dylan Snyder

          edit: oops. listen to shawn.

          • 2. Re: US zip code calculated field.
            Shawn Wallwork

            Dylan is correct, his formula is the equivalent to your Excel formula. But you didn't account for the possibility of a 4-digit zip with extra text, or a 4-digit ZIP-plus (8-digit ZIP). Here's the calc to handle those:

             

            IF ISNULL(INT(LEFT([Zipcode] + '00000',5)))

            THEN LEFT([Zipcode] + '00000',4)

            ELSE LEFT([Zipcode] + '00000',5)

            END

             

            --Shawn

             

            EDIT: I just notice that isn't the equivalent. With 4-digit ZIPs it adds a number to the end, not the beginning as your Excel formula does. Standby.

             

            Message was edited by: Shawn Wallwork

            • 3. Re: US zip code calculated field.
              Shawn Wallwork

              This turned out to be an interesting question. A couple of notes and then the solution.

               

              • Official ZIP Codes can have anywhere from 3 to 10 digits (including the "-")
              • Tableau recognizes ZIPs as ZIPs with and without the leading zeros
              • Tableau interpretes INT(1234-) as a number -1234 (interesting wrinkle)

               

              So then here's a formula to clean up 'dirty' ZIPs (as long as they start with the ZIP):

               

              IF CONTAINS([Zipcode],"-" )

              THEN LEFT([Zipcode],FIND([Zipcode],"-")-1)

              ELSE

              STR(IFNULL(INT(LEFT([Zipcode],5)),

              IFNULL(INT(LEFT([Zipcode],4)),INT(LEFT([Zipcode],3)))))

              END

               

               

              (See attached.)

               

               

              --Shawn

              1 of 1 people found this helpful
              • 4. Re: US zip code calculated field.
                philip.watkinson

                Greetings everyone. When I apply Shawn's formula to the data interpreter preview in Tableau 10.1 and try to create a calculated field, this is what I get:

                 

                Capture.PNG

                I apologize for not being able to show the original file since it is sensitive data. If there is anything that you can do to help me resolve this enigma that would be awesome.

                -Philip W