Remove leading zeroes

[#1]

Hi,

I wanted to remove leading zeroes from the filed loading from text file(Notepad). But it has negative numbers as well.

The following function is working fine only for positive numbers but not negative numbers.

Please suggest, if Anybody has worked with negative numbers before,.

  REPLACE(num(LTRIM(REPLACE(VARIANCE, '0',' '))),' ', '0')     ==> WORKING FOR POSITIVE NUMBER ONLY

VARIANCE

-000000000000219.34
000000000000219.34
000000000000219.34
-000000000000219.34
000000000000700.00
-000000000000700.00
-000000000000700.00
000000000000700.00
000000000000200.00
-000000000000200.00
-000000000000200.00
000000000000200.00
-000000000000480.66
000000000000480.66
000000000000480.66
-000000000000480.66
-000000000000361.38
000000000000361.38
000000000000361.38
-000000000000361.38
-000000000000000.30
000000000000000.30
-000000000000000.07
000000000000000.07
-000000000000000.05
000000000000000.05
000000000053407.96
-000000000053407.96

-000000000000004.72 

[Peter_Cammaert · Accepted solution]

Try this (QV accepts only numbers that are max 14 digits long)

  num#(mid(VARIANCE, FindOneOf(VARIANCE, '123456789.'))) * IF (Left(VARIANCE,1) = '-', (-1), 1)

Best,

Peter

[sunny_talwar]

May be this:

Table:

LOAD VARIANCE,

  Num(Num#(Left(Trim(VARIANCE), 1) & Right(Trim(VARIANCE), 9), '###0.00;-###0.00'), '#,##0.00;-#,##0.00') as NewVARIANCE;

LOAD * Inline [

VARIANCE

-000000000000219.34

000000000000219.34

000000000000219.34

-000000000000219.34

000000000000700.00

-000000000000700.00

-000000000000700.00

000000000000700.00

000000000000200.00

-000000000000200.00

-000000000000200.00

000000000000200.00

-000000000000480.66

000000000000480.66

000000000000480.66

-000000000000480.66

-000000000000361.38

000000000000361.38

000000000000361.38

-000000000000361.38

-000000000000000.30

000000000000000.30

-000000000000000.07

000000000000000.07

-000000000000000.05

000000000000000.05

000000000053407.96

-000000000053407.96

-000000000000004.72

];

[settu_periasamy]

Maybe try with fabs

REPLACE(num(LTRIM(REPLACE(fabs (VARIANCE), '0',' '))),' ', '0')

[#5]

Hi,

i thing this is the code which you looking for

=If(Left(Variance,1)='-',-1 * Num(Trim(Replace(mid(Variance,2),'0',''))) ,Num(Trim(REPLACE(Variance, '0',' '))))

thanks,

Pratyush Nautiyal

[MarcoWedel]

Hi,

other solutions could be (with or without keeping your two fixed decimal places):

table1:
LOAD *,
    Num(Evaluate(VARIANCE)) as VARIANCE2,
    Num(Evaluate(VARIANCE),'#0.00') as VARIANCE3
Inline [
    VARIANCE
    -000000000000219.34
    000000000000219.34
    000000000000219.34
    -000000000000219.34
    000000000000700.00
    -000000000000700.00
    -000000000000700.00
    000000000000700.00
    000000000000200.00
    -000000000000200.00
    -000000000000200.00
    000000000000200.00
    -000000000000480.66
    000000000000480.66
    000000000000480.66
    -000000000000480.66
    -000000000000361.38
    000000000000361.38
    000000000000361.38
    -000000000000361.38
    -000000000000000.30
    000000000000000.30
    -000000000000000.07
    000000000000000.07
    -000000000000000.05
    000000000000000.05
    000000000053407.96
    -000000000053407.96
    -000000000000004.72
];

hope this helps

regards

Marco