Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

How to shorten a zipcode formula from excel to alteryx

averyn
6 - Meteoroid

How would I enter this formula from excel: =IF(LEN(A1)>5,LEFT(A1,LEN(A1)-4),A1)  into the formula tool in alteryx? 

 

I have large Zip codes that I need to make only a 5 digit numeric zip code for another file to merge data together.

 

Thanks!

6 REPLIES 6
JohnJPS
15 - Aurora

Could you give this a try:

IIF (Length([A1]) > 5, Left([A1], Length([A1])-4), [A1])
MarqueeCrew
20 - Arcturus
20 - Arcturus
Left(a1,5) would work too
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
tom_montpool
12 - Quasar

@JohnJPS has given you mostly correct syntax to duplicate what you've got in Excel and create a numeric output field.

 

The Left and Length functions are String functions so if you want to return the result to a numeric field format you will need to convert the strings to numbers.

 

IIF (Length([A1]) > 5, ToNumber(Left([A1], Length([A1])-4)), ToNumber([A1]))

 

However...

 

I wonder if what you're doing in Excel is actually doing what you want...

 

If you have ZIPs that are longer than 5 characters, why not simply use ToNumber(Left([A1],5))?

 

If you use the Excel-style expression above, you would get a 6 digit code if your A1 has 10 characters (e.g. 01234-1234 would return 01234-). If you use the expression above and your A1 value has 7 characters, a Left([A1],5) would give you the same result as the Excel-style expression.

 

Also, what about A1 values less than 5 characters?

MKosmicki
8 - Asteroid

Very helpful! I was trying to figure out how to write this code so I could add a 0 in front of East coast zip codes that lost their leading zeros. My code is slightly different....

 

IF (Length([ZIP])) < 5 THEN '0'+[ZIP] ELSE [ZIP] ENDIF

 

Michelle

RodL
Alteryx Alumni (Retired)

@MKosmicki

Or you could just use...

PadLeft([ZIP], 5, '0')

  :-)

MKosmicki
8 - Asteroid

OH…Another arrow in the quiver!  Woo Hoo!

Labels