I'm trying to extract sub-string from a main string, e.g. extract "c1234" from "abc1234efg". In the past I can use a simple pattern "c\d*" to get what I need, but in Alteryx formula it seems doesn't have any way to do this. The closest way is use REGEX_Replace, but I have to change pattern to ".*(c\d*).*" plus use a mysterious "$1" in the end, which is much more complicate and counter-intuitive.
I know there is a tool called "RegEx", but it does not support concatenate pattern string according to previous fields.
Does anyone have any idea about how to fulfill this function? Thank you.
Thank you, I know this tool, but what I want to do is concatenate pattern string like this:
The "key" is one of input field of formula tool. Can RegEx tool also do this without creating macro?
Sorry about the misunderstanding.
But maybe your approach is the best I can think of/😁
Hi @luk
Your example does exactly what you're looking for.
REGEX_Replace([Field1], ".*("+[Key]+"\d*).*", "$1")
Here are the results with some sample data
As @Qiu said, there's no way to get the Regex tool to be dynamic without a macro. But why would you want to, when you have the solution using the formula tool?
Dan
Dan
Thanks for reply, I have this trouble because I wrote regex in both python and Alteryx, and the pattern syntax is very different between them. In this example, it's "c\d*" in Python but ".*(c\d*).*" in Alteryx, while the function is the same.
@luk I wanted to address the “mysterious $1” comment.
$1 is used to refer to the capture group in your expression. If you had more groups, they would increment $2, $3, etc.
This allows you to use your capture group and do something else with it once extracted. For example, you could do something like:
regex_replace(.........., ‘Ref#: ‘ +’$1’) to get a result of “Ref# C1234” if you were so inclined.
Hope that makes sense!
Thank you, I think this is the most tricky part. If I use pattern "c.*f" for string "abcdefghi", "$1" match nothing, while in other language, it will match "cdef". so if I copy the pattern from other language or devtool, I have to rewrite it to Alteryx format.
If you do
(c.*f)
then it should work. The parentheses identifies the capture group. Without the parentheses, $1 isn’t going to have anything “captured” from a match.
Not sure if we are talking about the same thing but your pattern def works.
Sorry but
(c.*f)
does not work on 2020.3.5, it must be like
.*(c.*f).*
I'm sure we are talking the same thing.