Remove the last two Characters in a field regardless of its length

[ajaygajree]

Hi

I have a field in Alteryx of varying Length, the last two digits are always numeric and all the preceding are Alpanumeric

e.g.

AB01

CD07

FGH07

HGLSD01

Please could someone help advise on how I could remove the last two digits in all my values in this column?

Best regards

Ajay

[pcatterson · Accepted solution]

Left([Field],length([Field])-2)

[MarqueeCrew · Accepted solution]

regex_replace([words],"(.*)..$","$1")

or 

left([words],length([words])-2) 

Cheers,

Mark

[ajaygajree]

Hi Mark

Thank you for your two solutions, can you point me to anywhere with further explanations on how to build regex expressions such as your first solution?

Ajay

[MarqueeCrew]

http://www.regular-expressions.info/

I also would search within the community for regex:

http://community.alteryx.com/t5/Alteryx-Knowledge-Base/RegEx-Perl-Syntax-Guide/ta-p/1288

so if you wanted to remove the last 2 characters only if they were digits:

(.*)\d{2}$ is looking for anything followed by two digits.

(.*)[.]\w{2,3}$ is looking for anything followed by a decimal point followed by 2 or 3 word characters at the end of the word. 

I put both solutions (regex and string functions) in case you wanted to see the equivalence.  While both are right, the string function is more clearly understood by all artisans.  The regex function likely performs faster and provides for much greater flexibility.

I encourage all users once they are comfortable with Alteryx and the use of functions to explore RegEx.  Install The Regex Coach and use it along with your workflows to help you with data cleanup.  I am self-taught in the use of the expressions.  I find posts helpful here and I use google to help me figure some solutions out too.

Thanks for your curiosity,

Mark