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

# US zip code calculated field.

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.

edit: oops. listen to shawn.

• ###### 2. Re: US zip code calculated field.

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.

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.

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:

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