Alteryx Designer

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

How to shorten a zipcode formula from excel to alteryx

Highlighted
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!

Highlighted
ACE Emeritus
ACE Emeritus

Could you give this a try:

IIF (Length([A1]) > 5, Left([A1], Length([A1])-4), [A1])
Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
Left(a1,5) would work too
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
ACE Emeritus
ACE Emeritus

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

Highlighted
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

Highlighted
Alteryx Alumni (Retired)

@MKosmicki

Or you could just use...

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

  :-)

Highlighted
8 - Asteroid

OH…Another arrow in the quiver!  Woo Hoo!

Labels