Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Leading 0's Help For Zip Codes

6 - Meteoroid



First post so hopefully this makes sense!  I have some very weird zip code data and I am trying to normalize it.


The data varies in length so I am trying to do it in multiple steps.


I want to trim everything to 5 digits and if it is shorter than 5 I want to add leading 0's.


TONUMBER(LEFT(TOSTRING([Service Zip Code]),5))      #trims to 5 spaces


PadLeft(tostring([Service Zip Code]),5,"0")  #tried and it throws an error saying that it needs to be a number


ToNumber(PadLeft(tostring([Service Zip Code]),5,"0"))  #works but doesn't add 0's to front because it is a number.  


Any help modifying this would be much appreciated.  Thank you!







15 - Aurora

Hey @Nova700


This is correct formula: PadLeft(tostring([Service Zip Code]),5,"0") 


I would suggest that you either add a select tool before the formula and make Zip code a string or create a new formula field (like Zip Code Formatted) which will stop the error.


If neither work, some sample data would be helpful

6 - Meteoroid

Thanks @LordNeilLord


So I was able to use Select to change the data type and PadLeft(tostring([Service Zip Code]),5,"0") did work, but whenever I add any additional code to cut the rest of the zip codes to 5 digits it then removed the 0 on the front.


The other issue I have is that if I trim some of the zip codes to 5 digits it is an invalid zip, an extra 0 needs to be removed from the end and added to the front.


ex: 88610000 needs to be 08861, not 88610


I have attached a partial sample of the data, hopefully that will help.

Alteryx Alumni (Retired)

I believe this is what you're looking for! Essentially, I trimmed off any leading and ending 0's in each string, then took the left 5 characters. If the character length was less than 5, I added leading 0's. Let me know if this works for your purpose.

6 - Meteoroid



That is really close to what I am looking for, the only modifications would be:


-If a number ends in 0 and has 5 character length then do nothing

-If a number ends in 0 and has 4 character length then only add 0 to the front

6 - Meteoroid

IF Length([Service Zip Code]) > 5
THEN Left([Service Zip Code],length([Service Zip Code])-4)


Could you do something like this?  Of course, that code isn't working for me malformed If (never done if/then in alteryx)


After that then then PadLeft(tostring([Service Zip Code]),5,"0")


Would that work?

Alteryx Alumni (Retired)

Try this! 

6 - Meteoroid

That seems to have done it, thank you so much!