Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

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

ajaygajree
7 - 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

4 REPLIES 4
pcatterson
11 - Bolide
Left([Field],length([Field])-2)
MarqueeCrew
20 - Arcturus
20 - Arcturus
regex_replace([words],"(.*)..$","$1")

or 

 

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

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
ajaygajree
7 - 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

MarqueeCrew
20 - Arcturus
20 - Arcturus

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 restart. Order shall return.
Please Subscribe to my youTube channel.
Labels