Formula to Clean Up US ZIP Codes

Version 2



    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)







    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.