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.
