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

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:
















Sample Output:





Formula Configuration:





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

8 - Asteroid

This is super helpful. Thank you!

7 - Meteor



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?



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!



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"))