Happy 8th birthday to the Maveryx Community! Take a walk down memory lane in our birthday blog, and don't miss out on the awesome birthday present that all Maveryx Community members get to take advantage of!
Going forward Alteryx is leveraging Mission Control to present Knowledge Base Articles. You’ll still be able to search Knowledge here in the Community, but you’ll be taken to Mission Control to read those articles. Learn more here.

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

How To: Remove Leading Zeros From a Field

Alteryx Alumni (Retired)
How To: Remove Leading Zeros from a Field
Option 1:
You can use the Formula Tool and the TrimLeft() function to remove leading zeros within an IIF statement to leave zero values.

Use the Formula tool - IIF([Field1] == "0","0", TRIMLEFT([Field1],"0"))

TrimLeft(String,"y") - Removes y from the beginning of String. Defaults to trimming whitespace.  "y" identifies what character you want to trim.
TrimLeft([Field1],"0") removes all 0 characters from the beginning of the string.
Note: Using Trim() will remove the string from the beginning and end of the string.

If you do not want to replace "0" with "", use the IIF statement to leave "0" records as "0" and TRIMLEFT the other records.

Sample Input:


Sample Output:


Formula Configuration:



Option 2:  Use Regex to Replace zeros only when there are 2 or more leading zeros.  

Regular Expression is \<0{2}+
\< Start of word
0 Specific Character
{2]+ two or more times

The Output Method is Replace and Replacement Text is blank.

Formula Configuration:

Option 3:
You can use the Formula Tool and the TrimLeft() function to remove leading zeros.
 If you do not want to replace 0 with "", filter all records that == '0', use TRIMLEFT() on all other strings, then union the results.

TrimLeft([Field1], "0")

Formula Configuration:



Formula Functions
Tool Mastery Formula
Introduction to Regular Expressions

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!



8 - Asteroid

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