In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

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

Zip Code

CherylH
8 - Asteroid

What is the formula to convert 4 digit zip code with a leading zero?

 

Ex 1249

Need result to be: 01249

 

Or 55373-0456

Needs to be 55373

6 REPLIES 6
alexnajm
18 - Pollux
18 - Pollux

PadLeft([Zip Code],5,"0")

alexnajm
18 - Pollux
18 - Pollux

For the second one, Left([Zip Code],5) should do the trick

alexnajm
18 - Pollux
18 - Pollux

You could combine them in a way to eensure one Formula tool addresses all concerns. BAsed on the two here, you could do IF Length([Zip Code])<5 THEN PadLeft([Zip Code],5,"0") ELSEIF Contains([Zip Code],"-") THEN Left([Zip Code],5) ELSE [[Zip Code] ENDIF

 

However this won't work for values like 5373-0456 so it can be made more robust if needed.

CharlieS
17 - Castor
17 - Castor

Hi @CherylH 

There's a couple useful functions that I would employ here:
Left( - this is a string function that takes the left characters from a string to the length you specify. Ex: Left('55373-0456',5) would give you 55373

Padleft( - a function that will add a character to the left side of a string to the length you specify. Ex: PadLeft('1249',5,'0') would give 01249

 

We can combine these functions so that one can take care of both scenarios. If your field is called [ZIP] you can use this:
PadLeft(Left([ZIP],5),5,'0')

 

This takes care of the situations above, but we can make this more robust. What about the value of 1249-5504 with a dropped leading 0, but the +4 characters on the end? The Contains( and FindString( functions would allow us to determine if a '-' character is present, and at what position. Using this and an additional Left( function. we could set up this IF statement:

IF Contains([ZIP],'-') THEN PadLeft(Left(Left([ZIP],FindString([ZIP],'-')),5),5,'0')
ELSE PadLeft(Left([ZIP],5),5,'0') ENDIF

 

So if a '-' is present, take the characters to the left of that position, then perform the other steps outlined above. I've put this in a workflow so you can see this all in action. Check it out and let us know if this helps.

RobertOdera
13 - Pulsar

Hi, @CherylH 

 

Try using the Pad in the Formula Tool to get to what I presume is your needed (Zip5).

PadLeft(ToString([Zip]), 5, '0')

 

-if you needed Zip6 for example, you would update the '5' to '6' in the formula - Cheers!

RobertOdera_0-1680279881909.png

 

CherylH
8 - Asteroid

Charles thank you for thinking for more zip code examples-good catch.  

 

The formula worked for 1 feed but not the other so I can field name for lolzip to zip and then it worked.  

 

Just trying to get this done quickly.

 

Thanks so much,

Cheryl

Labels
Top Solution Authors