removing leading zeroes in the cell

[DianaRumianceva]

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

[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 · Accepted solution]

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

[DianaRumianceva]

Thanks a lot Sean, it has worked :)