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.
Comments