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!

Error Message

Help

Preparation

Data Investigation

Accepted answers

echuong1

Try this!

New Workflow4.yxmd

All comments

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.

ServiceZipCodes.xlsx

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.

New Workflow4.yxmd

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703