Filter records with non alphabet characters (ie anything other than A to Z)
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
Solved! Go to Solution.
- Labels:
- Expression
- Regex
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You can change the pattern to:
[\w+|\s]+
a group of word characters OR space
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
