Alteryx Designer

Definitive answers from Designer experts.
Andy Uttley, Alteryx ACE, makes music with Alteryx | Math + Music

How To: Remove Leading Zeros From a Field

Alteryx
Alteryx
Created
How To: Remove Leading Zeros from a Field
 
You can use the Formula Tool and the TrimLeft() function to remove leading zeros.

 

TrimLeft(String,"y") - Removes y from the beginning of String. Defaults to trimming whitespace.

 

TrimLeft([Field1],"0") removes all 0 characters from the beginning of the string. If you do not want to replace 0 with "", filter all records that == '0', use TRIMLEFT() on all other strings, then union the results.

 

Note:  Using Trim() will remove the string from the beginning and end of the string.

 

Sample Input:

 

2019-02-28_17-19-29.png

 

 

 

 

 

 

 

 

 

 

 

 

 

Sample Output:

 

2019-02-28_17-21-02.png

 

 

Formula Configuration:

 

2019-02-28_17-22-30.png

 

 

You could also use the Regex tool to find and replace leading zeros. 

Attachments
Comments
8 - Asteroid

This is super helpful. Thank you!

7 - Meteor

Angelo

 

I'm a new comer to Alteryx and the community.  I notice your post for removal of leading zeroes was written back in 2016.  My question to you is would you the Select Function now to perform this?

 

Tim

8 - Asteroid

Wouldn't that RegEx replace all of the zeros?  (Not only the leading zeros)

7 - Meteor

Ms. Bindy

 

As I found out later, YES!!

 

Thanks for getting back!

 

Tim

5 - Atom

In my opinion, In the above scenario, if you do not want to replace 0 with "", while trimming leading 0, it would be simpler to use an IIF condition to check if Field1 == "0" and use the TRIMLEFT function in all other instances. You don't need to use any other tools.

 

IIF([Field1] == "0", "0", TRIMLEFT([Field1],"0"))

 

IIF_TRIMLEFT.png