Filter records with non alphabet characters (ie anything other than A to Z)

[jt_edin]

How do I filter a string field for anything other than alphabet chars? Case is not important. I'm unsure how to use CONTAINS or perhaps NOT CONTAINS with a list of A-Z, and I don't want to create 26 OR statements in the Filter tool. I'm sure regex can do this in a flash but I'm not sure where to get started.

If my field contains anything other than a-z or A-Z (eg punctuation) then I want to filter it. Thanks

ps - how would I do the above but to allow numbers 0 to 9 as well as letters of the alphabet?

[Federica_FF · Accepted solution]

If you use the formula

REGEX_Match([Your Column], "\w+")

in a filter tool, strings containing letters and/or numbers will be splitted in the T connector, anything else in the F connector.

\w means A-Z + a-z + 0-9 + underscore

[jt_edin]

Brilliant, thank you. And how would I add a space " " into the permitted characters also? I see from the cheat sheet that 's' means space, but how do I combine it with 'w'? Thanks

[Federica_FF]

You can change the pattern to:

[\w+|\s]+

a group of word characters OR space

[jt_edin]

Fantastic. What does the + on the end mean? Where's the best place to get started with regex in Alteryx? The help files make no sense to me. Thanks

[Federica_FF]

The + means that the entire group in this case (or generically what comes right before it) can be repeated 1 or more times. A sequence of letters and spaces.

If you don't put the + after the group, Alteryx will exclude a string that contains 2 spaces.

This page gives some pretty useful informations (it's linked somewhere in the Alteryx Regex help page) and this website TXT2RE you can write a string and it tries to give you all the possible code combinations. It can be useful. And in general there are a lot of resource on google, just be sure to find stuff about regex in PERL language, which is the one used by alteryx.

[VigneshKS]

You can drag and drop FORMULA to the screen and create a new column and mention the below query.

Uppercase((Regex_Replace(["Field"],"[^a-zA-Z0-9]",''))) 

Here I'm converting everything into capital letters, let me explain each query for you.

1. a-z -> will take all the small letters from a-z

2.A-Z -> will take all the capital letters from A-Z

3.0-9 -> will take all the numbers from 0 to 9

In your case you want to remove all special characters, so you can use the above query changing the field name.