How I can remove leading zeroes

[sparur]

Hello, colleagues.I have a small problem, which I can't resolve elegant.

I want to remove leading zeroes from my Data.

I tried to do some text / num converting, but it didn't help me.

See attached file

[sparur · Accepted solution]

I resolved my problem, but I do not like my solution. I believe that this problem can resolve more elegant.

somebody has any ideas? 🙂

[vupen]

Apparently, your column MATNR has string characters (alphabets). So its not a number in string format. This is why the num() is returning a NULL. You should first make MATNR a number only column, then apply a num() function.

[skaredovs]

Hi Anatoly,

I've tried different things, but seams like you solved it in almost shortest way. You can little simplify this code by using UPPER() and APPLYCODEPAGE() to get free of special letters.

Best regards,

Konstantins

[sparur]

Hello Upendra.

Thank for your reply, but I know why function num() is returning null value. I tried different converting variants 🙂

[sparur]

Hi, Konstantins.

Thank you for your idea with UPPER() function. I will apply it. but I don't understand how I can apply second function (applycodepage). How it will help me?

[skaredovs]

Hi, Anatoly.

I thought to use applycodepage() to get free of special characters, but it works not the way I want.

So, if you don't have special characters, then no problem.

Good luck,

Konstantins

[vupen]

May be this is what you are looking for. I have written a Macro function which takes the column value as the paramter and trims all the leading zeros.

[sparur]

Upendra, Thank you for your suggestion, but I don't like to use a macro. I use it only when I can't resolve problem without it. But your approach is solution also. Thank.

[guytzumer]

MATNR - you mean SAP I guess..?

if so.. the Characters that define MATNR (in the R3 system) is MIX with Numbers & Text

one solution is

TEXT(IF(INDEX(TEXT(Material_MATNR),'0000')=1,

NUM(RIGHT(TEXT(Material_MATNR),LEN(TEXT(Material_MATNR))-4)),TEXT(Material_MATNR))) as Material,