Formula to include some characters and exclude the remaining - Formula tool

Hi all,

I have a file with multiple columns and rows. Data about 57000. Here I am trying to filter out data that has some special characters. I have the list of allowed characters only, so anything apart from those characters, that particular field will be considered invalid and have to highlighted.

I wrote the below code in the multi-field formula tool:

ToNumber(if Contains(ToString([_CurrentField_]), "&") then "1"
elseif Contains(ToString([_CurrentField_]), "&") then "1" elseif
Contains(ToString([_CurrentField_]), "/") then "1" elseif
Contains(ToString([_CurrentField_]), "-") then "1" elseif
Contains(ToString([_CurrentField_]), "?") then "1" elseif
Contains(ToString([_CurrentField_]), ":") then "1" elseif
Contains(ToString([_CurrentField_]), "(") then "1" elseif
Contains(ToString([_CurrentField_]), ")") then "1" elseif
Contains(ToString([_CurrentField_]), ".") then "1" elseif
Contains(ToString([_CurrentField_]), ",") then "1" elseif
Contains(ToString([_CurrentField_]), "'") then "1" elseif
Contains(ToString([_CurrentField_]), "A") then "1" elseif
Contains(ToString([_CurrentField_]), "B") then "1" elseif
Contains(ToString([_CurrentField_]), "C") then "1" elseif
Contains(ToString([_CurrentField_]), "D") then "1" elseif
Contains(ToString([_CurrentField_]), "E") then "1" elseif
Contains(ToString([_CurrentField_]), "F") then "1" elseif
Contains(ToString([_CurrentField_]), "G") then "1" elseif
Contains(ToString([_CurrentField_]), "H") then "1" elseif
Contains(ToString([_CurrentField_]), "I") then "1" elseif
Contains(ToString([_CurrentField_]), "J") then "1" elseif
Contains(ToString([_CurrentField_]), "K") then "1" elseif
Contains(ToString([_CurrentField_]), "L") then "1" elseif
Contains(ToString([_CurrentField_]), "M") then "1" elseif
Contains(ToString([_CurrentField_]), "N") then "1" elseif
Contains(ToString([_CurrentField_]), "O") then "1" elseif
Contains(ToString([_CurrentField_]), "P") then "1" elseif
Contains(ToString([_CurrentField_]), "Q") then "1" elseif
Contains(ToString([_CurrentField_]), "R") then "1" elseif
Contains(ToString([_CurrentField_]), "S") then "1" elseif
Contains(ToString([_CurrentField_]), "T") then "1" elseif
Contains(ToString([_CurrentField_]), "U") then "1" elseif
Contains(ToString([_CurrentField_]), "V") then "1" elseif
Contains(ToString([_CurrentField_]), "W") then "1" elseif
Contains(ToString([_CurrentField_]), "X") then "1" elseif
Contains(ToString([_CurrentField_]), "Y") then "1" elseif
Contains(ToString([_CurrentField_]), "Z") then "1" elseif
Contains(ToString([_CurrentField_]), "0") then "1" elseif
Contains(ToString([_CurrentField_]), "1") then "1" elseif
Contains(ToString([_CurrentField_]), "2") then "1" elseif
Contains(ToString([_CurrentField_]), "3") then "1" elseif
Contains(ToString([_CurrentField_]), "4") then "1" elseif
Contains(ToString([_CurrentField_]), "5") then "1" elseif
Contains(ToString([_CurrentField_]), "6") then "1" elseif
Contains(ToString([_CurrentField_]), "7") then "1" elseif
Contains(ToString([_CurrentField_]), "8") then "1" elseif
Contains(ToString([_CurrentField_]), "9") then "1" elseif
Contains(ToString([_CurrentField_]), "Carriage Return") then "1" elseif
Contains(ToString([_CurrentField_]), "Line Feed") then "1" elseif
Contains(ToString([_CurrentField_]), "+") then "1" elseif
Contains(ToString([_CurrentField_]), "|") then "0" elseif
Contains(ToString([_CurrentField_]), "^") then "0" elseif
Contains(ToString([_CurrentField_]), "`") then "0" elseif
Contains(ToString([_CurrentField_]), "Ú") then "0" elseif
Contains(ToString([_CurrentField_]), "ú") then "0" elseif
Contains(ToString([_CurrentField_]), "ô") then "0" elseif
Contains(ToString([_CurrentField_]), "Ô") then "0" elseif
Contains(ToString([_CurrentField_]), "é") then "0" elseif
Contains(ToString([_CurrentField_]), "É") then "0" elseif
Contains(ToString([_CurrentField_]), "è") then "0" elseif
Contains(ToString([_CurrentField_]), "È") then "0" elseif
Contains(ToString([_CurrentField_]), "À") then "0" elseif
Contains(ToString([_CurrentField_]), "à") then "0" elseif
Contains(ToString([_CurrentField_]), "Ó") then "0" elseif
Contains(ToString([_CurrentField_]), "ó") then "0" elseif
Contains(ToString([_CurrentField_]), "ö") then "0" elseif
Contains(ToString([_CurrentField_]), "Š") then "0" elseif
Contains(ToString([_CurrentField_]), "Â") then "0" elseif
Contains(ToString([_CurrentField_]), "Ä") then "0" elseif
Contains(ToString([_CurrentField_]), "Ü") then "0" elseif
Contains(ToString([_CurrentField_]), "Î") then "0" elseif
Contains(ToString([_CurrentField_]), "#") then "0" else "0"
endif)

Can someone please help me with either altering the existing formula or with alternate solutions in order to achieve the same.

Thanks & regards,
Vandhana

Rejections.PNG

Transformation

Accepted answers

OTrieger

Hello @Vandhana_Chandran

You can use Filter tool to get the job. Tick on Custom:
Then select the field that contains the text, let say in Description and write the following formula:
[Description ] not in ("^","Ú","à"........)

In this way you are creating a list for Alteryx to use and it will look for all these items that you will define in the list and will filter base on them.

All comments

griffinwelsh

@Vandhana_Chandran This is a job for the regex tool. You can parse the filter characters you want and then filter on if your parse field is null. If you post a dataset of all characters that you want to flag I will build a sample workflow for you.

OTrieger

Vandhana_Chandran

ToNumber(if Contains(ToString([_CurrentField_]), ";") then "0"
elseif Contains(ToString([_CurrentField_]), "#") then "0" elseif
Contains(ToString([_CurrentField_]), "|") then "0" elseif
Contains(ToString([_CurrentField_]), "^") then "0" elseif
Contains(ToString([_CurrentField_]), "`") then "0" elseif
Contains(ToString([_CurrentField_]), "Ú") then "0" elseif
Contains(ToString([_CurrentField_]), "ú") then "0" elseif
Contains(ToString([_CurrentField_]), "ô") then "0" elseif
Contains(ToString([_CurrentField_]), "Ô") then "0" elseif
Contains(ToString([_CurrentField_]), "é") then "0" elseif
Contains(ToString([_CurrentField_]), "É") then "0" elseif
Contains(ToString([_CurrentField_]), "è") then "0" elseif
Contains(ToString([_CurrentField_]), "È") then "0" elseif
Contains(ToString([_CurrentField_]), "À") then "0" elseif
Contains(ToString([_CurrentField_]), "à") then "0" elseif
Contains(ToString([_CurrentField_]), "Ó") then "0" elseif
Contains(ToString([_CurrentField_]), "ó") then "0" elseif
Contains(ToString([_CurrentField_]), "ö") then "0" elseif
Contains(ToString([_CurrentField_]), "Š") then "0" elseif
Contains(ToString([_CurrentField_]), "Â") then "0" elseif
Contains(ToString([_CurrentField_]), "Ä") then "0" elseif
Contains(ToString([_CurrentField_]), "Ü") then "0" elseif
Contains(ToString([_CurrentField_]), "°") then "0" elseif
Contains(ToString([_CurrentField_]), "Î") then "0" else "1"
endif) - I used this formula in the formula tool to achieve the same.

List of characters that are not allowed - ; , # | Î ° Ü Ä Â Š ö ó Ó à À È é Ô ô ú Ú ` ^
These are just a few that are not allowed there could be many more apart from these.

Allowed characters : Any character apart from these have to be highlighted.

I was able to achieve using the formula tool but I would personally opt for RegEx tool. Just that I am new to Alteryx I am not sure of the syntaxes.

I am eager to learn the solution using RegEx.

Thanks

Vandhana_Chandran

Thank you @OTrieger

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703