How to Use Regex in the Alteryx Formula Tool

The dedicated RegEx tool is great when you want to match, parse, replace or tokenize text. Sometimes, though, Regex is only one part of a larger calculation. You might want to use it inside an IF statement, combine it with other cleaning steps, classify records or create a calculated field. In those situations, it often makes more sense to use Regex directly in the Formula tool.

The three main Regex functions available there are:

• REGEX_Match()
• REGEX_Replace()
• REGEX_CountMatches()

REGEX_Match()

REGEX_Match() checks whether a text value matches a Regex pattern. The basic syntax is:

REGEX_Match(String, Pattern, icase)

For example:

REGEX_Match([Reference], "[A-Z]{3}-\d{4}")

This checks whether the value in [Reference] follows a structure such as:

FUR-1048

The optional icase argument controls whether the match is case-sensitive:

• 1 ignores case
• 0 requires the case to match

If you leave the argument out, Alteryx ignores case by default.

The Important Thing to Know About REGEX_Match

The main thing to watch out for is that REGEX_Match() is not automatically a “contains” function. It expects your Regex pattern to account for the whole contents of the field, from the first character to the last.

Suppose [Description] contains:

Contact matched using Email

This formula:

REGEX_Match([Description], "Email")

does not simply check whether the word Email appears somewhere in the text. The pattern does not account for everything before it. To allow other text before and after Email, you would use:

REGEX_Match([Description], ".*Email.*")

The first .* allows any text before Email, while the second allows any text after it. This is a very easy difference to miss, particularly when moving between the dedicated RegEx tool and the Formula tool.

REGEX_Replace()

REGEX_Replace() finds text matching a pattern and replaces it. Its syntax is:

REGEX_Replace(String, Pattern, Replacement, icase)

For example, you could remove every non-numeric character from a telephone number using:

REGEX_Replace([Phone], "\D", "")

This would turn:

020 7946-0123

into:

02079460123

REGEX_Replace() replaces every occurrence of the matching pattern, rather than stopping after the first.

REGEX_CountMatches()

REGEX_CountMatches() counts how many times a pattern appears within a string.

Its syntax is:

REGEX_CountMatches(String, Pattern, icase)

Suppose [Match Reasons] contains:

Email, Phone, Fuzzy Email

This formula:

REGEX_CountMatches([Match Reasons], "Email")

returns:

2

That is because Email appears once by itself and once inside Fuzzy Email. This function is useful for things such as:

• Counting IDs inside a description
• Finding repeated separators

When Should You Use the Formula Tool?

Regex in the Formula tool is particularly useful when:

• The result needs to feed into an IF statement
• Regex is only one part of a larger calculation
• You want to count how many matches appear
• You are combining replacement logic with other cleaning steps

The dedicated RegEx tool is usually the better option when:

• You want to parse several captured values into separate columns
• You want to tokenize repeated matches into rows or columns
• Regex is the main purpose of the transformation

Quick Reference

Summary

The Formula tool gives you three particularly useful ways to work with Regex:

• Use REGEX_Match() to test a pattern
• Use REGEX_Replace() to change or remove matching text
• Use REGEX_CountMatches() to count occurrences

The biggest thing to remember is that REGEX_Match() expects the pattern to account for the whole field. When you want to find something anywhere inside a longer value, you will often need a pattern such as:

.*Target Text.*

This will find that pattern no matter what is surrounding it in the cell.