Punctuation/Special Character wildcard?

[i0r003k]

Hello All,

Short Version:

I am wondering if Alteryx has a Punctuation/Special Character wildcard (or a way to simulate one)?

Long Version:

I am working on a flow that reads a list of addresses (street, PO Box, and Street & PO Box) and pulls out the PO Box when present. Since the addresses are entered by a large group of users, there is no consistent format in how they are entered.

I started, in short, with a data cleansing tool to remove punctuation, then a formula to replace spaces with commas, leading into a parsing tool, and finally a formula that tests for "PO" in the parsed columns and when found combines that column with the following 2 to generate the PO Box address.

The two issues I am running into are that the data cleansing tool does not remove special characters and when it removes punctuation it does not allow for whether or not a space is left.

So, "PO Box 123- 456 Main St" ends up returning "PO Box 123-",

and "PO Box 123,456 Main St" ends up returning "PO Box 123456", neither of which is correct.

I came up with the following formula to check for a space and replace the punctuation/special character accordingly, but would rather not have to replicate it for every possible punctuation mark and special character (and combination thereof):

IF Contains([Address],"/")
AND (Contains([Address]," /") OR Contains([Address],"/ ") )
THEN Replace([Address],"/","")
ELSE Replace([Address],"/"," ")
ENDIF

Is there a way to check for all unwanted characters when running this? I am also open to completely reworking the flow if someone knows another way to extract the data I need.

[DawnDuong · Accepted solution]

Hi @i0r003k 

you can use the || within Regex to do this.

our super @NicoleJohnson has a very elegant solution for this

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/RegEx-alternative-to-multiple-Contains...

dawn 

[Maskell_Rascal]

Hi @i0r003k 

Can you post some sample data for us to troubleshoot? As @DawnDuong said there is most likely a RegEx solution, but I'd need to see some examples from your dataset to write the formula/pattern. 

Thanks!

Phil

[i0r003k]

Thank you! That worked beautifully.