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.