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!
Solved! Go to Solution.
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
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.
.@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
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?
That seems to have done it, thank you so much!