community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

How to shorten a zipcode formula from excel to alteryx

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!

ACE Emeritus
ACE Emeritus

Could you give this a try:

IIF (Length([A1]) > 5, Left([A1], Length([A1])-4), [A1])
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.
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?

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')

  :-)

Asteroid

OH…Another arrow in the quiver!  Woo Hoo!

Labels