Regex Expression not behaving as expected/desired

[tom_montpool]

I have been trying to get a Regex expression to work and just can't seem to figure out the correct syntax. Rather than waste more time, I'm opening it up to the Community...

Sample Records:

EAG_CAN_2017_Apples1_A

EAG_CAN_2018_Oranges15_B

Desired Output:

EAG_CAN_2017_Apples_A

EAG_CAN_2018_Oranges_B

Syntax I've tried:

Regex_Replace([Field],"(.*)\d+(.*)","$1"+"$2")

Regex_Replace([Field],"(.*)\d{1,2}(.*)","$1"+"$2")

Regex_Replace([Field],"(.*)\d{1,2}?(.*)","$1"+"$2")

Regex_Replace([Field],"(.*)(\d{1}|(\d{2})(.*)","$1"+"$3")

The problem:

It seems that no matter what I try, the "1" in the "15" is captured in the first (.*) expression. I'm particularly surprised that \d{1,2} doesn't work. The Regex help (RegEx Perl Syntax Guide) indicates that this structure matches "between n and m times inclusive"...

I'd appreciate any ideas...

[MarqueeCrew]

hey @tom_montpool,

how about:

Regex_Replace([data],"(.*)\d+(_.)",'$1$2')

it works with this data....

Cheers,

Mark

[tom_montpool]

When I run your version with _. instead of .*  in the last matched expression, I still get:

EAG_CAN_2017_Apples_A
EAG_CAN_2018_Oranges1_B

I'm running Alteryx Version 10.6.8.17850...

[jdunkerley79 · Accepted solution]

I'd go for:

REGEX_Replace([Field1],"^(.*\D)\d+(_.*)$","$1$2")

I think for @MarqueeCrew answer you need to add a ? to the .* to make it not greedy then it will work

[MarqueeCrew]

Regex_Replace([data],"(.*?)\d+(_.)",'$1$2')

This works better :)

as @jdunkerley79 also described.  2 answers for the price of 1