Remove Leading Zeros in Alteryx

This week, I ran into a data engineering problem where I needed to extract a JV number from a string of numbers. The JV number can have 3,4, or 6 digits. For any number less than 6 digits, the system adds leading zeros. At first, I thought I needed to dig into some Regex, but there was a much simpler answer to remove leading zeros in Alteryx. Read on to add another tool to your Alteryx toolbox.

Now, I learned the Spotfire expression language before I learned Alteryx syntax. Spotfire used the Trim function to remove whitespace, so when I learned Alteryx, I looked for the same expression. Alteryx has a Trim function and it does in fact remove whitespace. In fact, the default configuration is to remove whitespace with Trim. Because I thought I knew what Trim did, I never dug any further. However, that’s not all you can do with Trim.

The Trim Function

The Trim function removes the character(s) specified from the ends of the string. Y is optional, and the function defaults to trimming white space. Click on the link for additional examples.

Trim(String, y) -- basic syntax

Trim([JV Number], "0") -- my expression

There are also options to TrimLeft and TrimRight, which is what I used in my use case. TrimLeft removes characters from the beginning of the string, and TrimRight removes characters from the end of the string.

Remove Leading Zeros

TrimLeft did the job perfectly because zeros were always added to the start of my JV Number.

And that is how you can quickly and easily remove leading zeros in Alteryx!

Other Sweet Alteryx Content

1 thought on “Remove Leading Zeros in Alteryx”

  1. Pingback: Update SharePoint Drop Down Lists Using Alteryx Tools » The Analytics Corner

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.