If you’re here, you’ve been trying out Alteryx (hopefully enjoying it as well), and want to take your skills to the next level. One powerful tool to accomplish this is the Alteryx regex tool. The Alteryx regex tool can be used within your workflow to help parse, match, or replace data. You do this by using syntax patterns within the data that Alteryx can recognize. Whether you’re interesting for the sake of learning of have a project that needs to be done ASAP, I hope you enjoy figuring out how this amazing tool works.
Setting Up Your Workflow
First things first, if you want to follow along with me, download this dataset (Regex Example) and set up your input within a new workflow in Alteryx.
Now we got the bare minimum on the page, navigate to the ‘parse‘ drop-down within the tool menu and you should see the following:
As you might have guessed, drag the tool that says ‘RegEx‘ onto your input and we can start getting into the fun part.
Don’t be alarmed by the error message, all we have to do is decide what we want to do with our tool. If you take a look at the data, you’ll see something along the lines of this:
The main goal I am hoping to accomplish with this tutorial is getting you used to the patterns/syntax of the Alteryx regex tool. After that, I’ll link to a tutorial where I cover the three common uses (parse, match, replace).
Alteryx Regex Tool Syntax
Click on your regex tool and navigate to the left panel where you configure your tool. For this example, we will be using the ‘Address‘ field as our Column to parse. The area where we will be inputting our pattern syntax in the ‘Regular Expression‘ section. I don’t expect you to learn all the syntax off by heart, and luckily, neither does Alteryx. If you click on the plus to the right side, Alteryx automatically shows the different options for their patterns:
What you now need to know is how to actively use the syntax. The most common pattern you’ll use is likely (.*). Looking up in the table, you can see this means any single character (Zero or more). This essentially combines the two symbol to indicate that you are referencing any characters zero or more in length. So, this means you are looking for everything in that column. The brackets are also important as Alteryx will be able to recognize this as one grouping. This gets very important when you start to want to split up your column’s records. For example, if you wanted to split up the address by the commas you would input (.*),(.*),(.*). You might be able to guess that Alteryx will comb through the characters until it finds a comma. (or whatever you put between your columns).
Regex Tool Example
Say, for example, you wanted the first numbers of the address, whatever is in between, and the numbers after the dash. the syntax would look something like this:
The Alteryx RegEx Tool will look for any number of digits. Once there are no more digits, it will go through any number of characters till it reaches a ‘-‘. Finally, it will go through the remaining characters after the dash. Although you might not be familiar with them yet, you might start to see just how flexible this can be when we think about spiting or replacing specific sections of our data.
Other Useful Syntax For The Regex Tool
Some more very useful syntax are the specific character types, such as the one for digits (\d) that I used above. Also, the one for a new line (\n), white space (\s), and tab (\t). There are also good ones for lower case (\l) and upper case (\u) characters. The final syntax I want to show is the start of work (\<), end of word (\>), and the use of {} to specify a specific number of characters you want (\d{3} returns the first 3 digits).
Final Example
To finish off, an example specifying two of the addresses in the list. It might look like a complete mess but if you go through it 1-by-1 you will be able to decipher it. I’ll give you a hint as well, one of the addresses is 2316 E 5th Ave,Denver,CO 80206-4205.
There is a lot more complex uses for this syntax depending on your situation. I hope, at least, that this gives you a brief but insightful overview of how to get started working with the Alteryx RegEx Tool. To continue learning the different outputs you can achieve with this tool, stay tuned for my follow up tutorial coming on the 11th.
Thanks for reading check out my page if you want to learn more: Darby Lehane
