alteryx Community

# Alteryx Designer Desktop Discussions

SOLVED

## How to shorten a zipcode formula from excel to alteryx

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
15 - Aurora

Could you give this a try:

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

Chaos reigns within. Repent, reflect and restart. Order shall return.
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?

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

Alteryx Alumni (Retired)

@MKosmicki

Or you could just use...