Sometimes it is very practical to be able to change a part in a string value (like what you do with Find and Replace in MS Words). Ever since I got to know REGEX, I learned how to do it in Alteryx. This blog will show you two ways of doing it.

To start with, let’s take an example from Challenge #58, I think it is a good one. So here, the dates are in string value, with are needed to be transformed. But to do so, first we need to replace all the “1” in the beginning of the line with “20” and the “0” with “19” to have the correct year.

First way: Using REGEX_Replace in the Formular tool

I found out about this last week, and was fascinated how powerful it was. The syntax is REGEX_Replace(String, pattern, replace), where you can define the String you want to find & replace, the pattern you want to replace and the new string to replace with.

In this example, I put in the hat “^” before number “0” and “1” to say only replace the “0” and the “1” which are in the beginning of the line. And I replace all the “1” first because if I do the “0” first, I will have more “1” coming from the replaced value “19” .

Second way: Parsing Tool- RegEx

The other tool with the same function is “RegEx” under “Parsing”. In the configuration, choose:

- Column to parse

- The part you want to replace under Regular Expression (put it in brackets)

- Under Output Method, choose “Replace” and type in the Replacement text in the box.

Here you have to do it twice, one for the “1” and one more for the “0”, by putting two RegEx tools one after the other.

The result with look like this in either case:

IT IS IMPORTANT TO NOTE THAT the original column is string value with the size of “7”. So in order not to loose date, put a Formula tool in the begin to change the size to “8”.

So it is two ways to replace data using RegEx. I hope this is helpful. Have fun!