community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

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

Meteor

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

ACE Emeritus
ACE Emeritus
Left([Field],length([Field])-2)
Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
regex_replace([words],"(.*)..$","$1")

or 

 

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

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Meteor

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

Alteryx Certified Partner
Alteryx Certified Partner

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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Labels