Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

How To: Remove Leading Zeros From a Field

AngelaO
Alteryx
Alteryx
Created
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:

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

Sample Output:

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

Formula Configuration:

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

 

Formula Functions
Tool Mastery Formula
Introduction to Regular Expressions

Attachments
Comments
marlline
8 - Asteroid

This is super helpful. Thank you!

Tim_at_Ford
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

MsBindy
8 - Asteroid

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

Tim_at_Ford
7 - Meteor

Ms. Bindy

 

As I found out later, YES!!

 

Thanks for getting back!

 

Tim

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

 

IIF_TRIMLEFT.png