Keeping part of a string

[nwatzlaf]

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

[Ladarthure · Accepted solution]

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]

Hi @nwatzlaf 

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

Cheers,

[grossal · Accepted solution]

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]

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]

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

[Thableaus]

@nwatzlaf 

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

Cheers,

[nwatzlaf]

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

Thanks!!

[Ladarthure]

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