Formula to Clean Up US ZIP Codes

Version 2

    Description:

     

    This formula will clean up extra characters that are sometimes appended to ZIP Codes. It also converts a ZIP+ (10-digits) to a 5-digit ZIP that Tableau will recognize.

     

    Example Calculation:

     

    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


    Comments:

     

    This is written for US ZIP Codes. The only pre-condition is that the ZIP must be the first x-digits in the string.

     

    Considering the following:

     

    • 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)

     

    If it weren't for this last item the STR() statement would be sufficient. But since INT(1234-) doesn't return a NULL value, those need to be dealt with first.