How To: Remove Leading Zeros From a Field

[AngelaO]

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:

image.png

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:
image.png

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:

image.png

 

Formula Functions
Tool Mastery Formula
Introduction to Regular Expressions

[marlline]

This is super helpful. Thank you!

[Tim_at_Ford]

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

[MsBindy]

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

[Tim_at_Ford]

Ms. Bindy

As I found out later, YES!!

Thanks for getting back!

Tim

[p-g]

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