Leading 0's Help For Zip Codes

[Nova700]

Hello,

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!

[LordNeilLord]

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

[Nova700]

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.

[echuong1]

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.

[Nova700]

.@echung1

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

[Nova700]

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?

[echuong1 · Accepted solution]

Try this! 

[Nova700]

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