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

Keeping part of a string

nwatzlaf
8 - Asteroid

Hi there,

 

I am looking for a way, I think it is in RegEx but not positive, to keep part of a string. I want to keep the first 5 characters of a string (Zip code). Some are extended and written as 15241-2344. Some are written correctly as 15241. Either way I need the whole column to contain zip codes written with just 5 characters.

 

Thank you for your time,

Natalia

8 REPLIES 8
Ladarthure
14 - Magnetar
14 - Magnetar

hi @nwatzlaf,

 

an easy way to do so would be to use a formula left([zipcode], 5) which should work perfectly from what you described!

Thableaus
17 - Castor
17 - Castor

Hi @nwatzlaf 

 

Left([ZipCode], 5) should do the trick if the format is consistent to the first 5 characters of the column.

 

Cheers,

grossal
15 - Aurora
15 - Aurora

Hi @nwatzlaf,

 

you are right, you can use the Regex-Tool for this, but you could also use a Formula Tool and the 'Left' function.

 

Formula:

 

Left([Column], 5)

 

 

Regex:

 

^(\d{5}).*

(with Parse option)

 

 

Let me know if this works for you!

 

Best

Alex

nwatzlaf
8 - Asteroid

That worked thank you! 

 

Do you know how I would inject a leading 0 if there are 4 characters present instead of 5?

 

Thanks!

Ladarthure
14 - Magnetar
14 - Magnetar

you can use the padleft function (padleft([zip],5,'0')

Thableaus
17 - Castor
17 - Castor

@nwatzlaf 

 

You can use PadLeft(ZipCode, 5, "0") to put an extra 0 if you wish.

 

Cheers,

nwatzlaf
8 - Asteroid

Now will this only affect the strings with 4 characters? I want the ones with 5 characters to remain as is.

 

Thanks!!

Ladarthure
14 - Magnetar
14 - Magnetar

It will, it will oinly add 0 if there are less than 5 characters.

Labels