Regex in Alteryx: Using It in the Filter and Formula Tools

Most people meet regex in Alteryx through the RegEx tool, but you can also use it directly inside the Filter and Formula tools.

This blog shows how to use REGEX_Match and REGEX_CountMatches in the Filter and Formula tools, what each one returns, and how to write patterns that give you the result you want. It finishes with a worked example that counts titles like Miss, Ms and Mr.

The two functions to know:

• REGEX_Match(String, Pattern, icase) returns True or False: does the whole string match the pattern?
• REGEX_CountMatches(String, Pattern, icase) returns a number: how many times does the pattern appear in the string?

Regex in the Filter tool

The Filter tool needs an expression that comes out True or False, and REGEX_Match already gives you exactly that. You don't need to compare it to anything.

For example, to keep only the rows that start with M:

REGEX_Match([Field1], 'M.*', 0)

Rows that start with M go out of the T output and everything else goes to F.

This pattern has to match the whole string. So M.* means "starts with M, followed by anything". It also means that to find a word anywhere in the text, you need .* on both sides:

REGEX_Match([Field1], '.*Mr .*', 0)

The space after Mr stops Mrs from matching.

Regex in the Formula tool

In the Formula tool you usually want a new column rather than a filter. The rule that matters most: write the result to a new field, never back into the field you're testing. If you overwrite [Field1] with REGEX_Match, your text is replaced by True/False and it's gone.

To flag rows, use a new Bool field:

REGEX_Match([Field1], 'M.*', 0)

To get a 1 or 0 you can add up later, use a new Int32 field:

IIF(REGEX_Match([Field1], 'M.*', 0), 1, 0)

Counting with REGEX_CountMatches

REGEX_CountMatches counts how many times a pattern appears in each row. Keep the pattern only as long as the thing you're counting. .* is greedy, meaning it takes everything to the end of the string, so a pattern ending in .* can only ever match once per row.

Patterns for common counts:

The Formula tool works one row at a time, so it can't give you a total across rows. For that, add a Summarize tool afterwards and Sum your 1/0 field.

Worked example: counting Miss, Ms and Mr

If I had a column of names with titles, and I wanted to know how many had Miss, Ms or Mr. The only other title was Sir.

Field1
Mr John Smith
Miss Amy Jones
Sir Ian Brown
Ms Priya Patel
Mr Tom Lee

Every title I wanted starts with M and Sir doesn't, so "starts with M" is enough. In a Formula tool:

IIF(REGEX_Match([Field1], 'M.*', 0), 1, 0)

After this, put a Summarize tool after it and Sum IsMTitle. On the sample above, the answer is 4.

To get a separate count for each title, use a RegEx tool in Parse mode with .*?(Miss|Ms|Mr) .*. Then in a Summarize tool, Group By the new column and Count.

What the 0 or 1 at the end does

The optional third argument controls case sensitivity, and by default it's off.

• 0: case-sensitive. M matches only a capital M.
• 1: case-insensitive. M matches M and m.

REGEX_Match('mr smith', 'M.*', 0)   → False
REGEX_Match('mr smith', 'M.*')      → True

I add the 0 by habit now. It's easy to forget that Alteryx ignores case by default, and a lowercase value can quietly inflate a count.

Cheat sheet