Length of fields

[Kavya432]

Hi Team,

I am looking for a logic for the below requirement I have.

I have a field called prod_no

If length of pros_no = 5 then I should add '000' to the left to make it 8 digit number

for example if prod_no = 12345 then result should be like 12345000

If length of prod_no = 4 then I should add 000 to the left and 0 to the right

for example if prod_no = 1234 then result should be like 01234000

If length of prod_no = 3 then I should add 000 to the left and 00 to the right

for example if prod_no = 123 then result should be like 00123000

If length of prod_no = 2 then I should add 000 to the left and 000 to the right

for example if prod_no = 12 then result should be like 00012000

If length of prod_no = 1 then I should add 000 to the left and 0000 to the right

for example if prod_no = 123 then result should be like 00001000

Appreciate if you can give formula for this problem.

Other questions is for same field prod_no I have Characters and alpha numeric characters in it. How I can filter those out.

for example prod_no = 0nanana000

                                     H290478

Thanks

Kavya

[Paul-Evans]

Are you using a string data type for the output? A numeric type will drop leading zeros.

[MarqueeCrew]

Thank you @DanielG .

 Hopefully we both get solution credits. 

cheers,

 mark

[Kavya432]

Its working with the formula. But when I tried to copy to excel some how the zero's to the left side of Prod_no is disappearing(do we need to change any datatype?). 

Thanks all for the solutions!

Other quick question if Base_Prod_No has zero's or alpha numeric characters or if has seven characters is there any way we can replace with blank?

[Kavya432]

forgot to attach the data file

Other quick question if Base_Prod_No has zero's or alpha numeric characters or if has seven characters is there any way we can replace with blank?

[Kavya432]

Its not working if I use the  IsNumber([Base_Prod_no]), its not bringing any data trough.

[DanielG]

It has to be a string to retain the lead zeros.

A numeric field will drop those as they are not relevant when interpreting it as a number.

I tested by creating a text file with this in it: 

test    *   
1 0000111100000
2 11110000
3 0101010

I used Output tool to drop it was an Excel to my desktop, and it retained the string structure.

You just need to make sure it is a string before you push it down to its final location.

If you copy and paste it from the results window to Excel, it will be translated into a number.  So dont do that.  🙂

[DanielG]

use to be clear on my last post, the 1,2,3 at the front of each item is the row# not the lead digit of what I was testing...

Didnt realize how bad that looked when I typed and posted it initially...  

[DanielG]

For your second question:

I am sure there is a more elegant way to do it, but perhaps a formula on that column with "if contains(field, "0") then "" else y endif (with additional layers if needed)

Please note that is above is roughly written and will require syntax tweaking to make it accurate.

[Kavya432]

No Problem, will try to output to excel.

Could you please help me to remove any alpha numeric and zeros and just leave a empty or blank field in Base_Prod_no.