How to shorten a zipcode formula from excel to alteryx

[averyn]

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!

[JohnJPS · Accepted solution]

Could you give this a try:

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

[MarqueeCrew]

Left(a1,5) would work too

[tom_montpool]

@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]

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]

@MKosmicki

Or you could just use...

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

  :-)

[MKosmicki]

OH…Another arrow in the quiver!  Woo Hoo!