removing leading zeroes in the cell

Hi all,

I tried different tools to remove leading zeros in the column but all i get is the error message or blank field as a result. The example of the value in in cell 000000004066076, 000002030781626, 000001757717133. The amount of leading 0 could vary

I have tried:

RegEx with expression ^0+ - give me the blank field

I have tried Formula (TrimLeft [column name],0) - type mismatch.

Please help tried Gemini to ask but it suggest to use text to column, which doesn't work either.

Please help

Alteryx Practice

Accepted answers

Sean_Dauzat

Make sure to wrap the 0 in quotations like: TrimLeft([column name],"0")

All comments

DataNath

Hey @DianaRumianceva, I'm guessing from the type mismatch error you're receiving that the field is coming in as a numeric data type. If so, you could use something like the following:

TrimLeft(ToString([Input]),'0')

Where you temporary cast the field as a string so you can apply string functions to it. If you want the output to be numeric still you can wrap this again in ToNumber(), like so:

ToNumber(TrimLeft(ToString([Input]),'0'))

Alternatively, you can use a Select tool and just change the data type to something like a double - should automatically strip the leading 0s.

Sean_Dauzat

Make sure to wrap the 0 in quotations like: TrimLeft([column name],"0")

DianaRumianceva

Thanks a lot Sean, it has worked

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703