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

[fmvizcaino]

Hi @Kavya432 ,

Attached is an example showing how to do it. 

Let me know if this works for you.

BEst,

Fernando Vizcaino

[DanielG]

If Length([Field]) = 5 then [Field]+"000" elseif

Length([Field]) = 4 then "0"+Field+"000" elseif

Length(Field]) = 3 then "00"+Field+000" elseif

Length(Field)= 2 then "000"+FIeld+"000" elseif

Length(Field)=1 then "0000"+Field+"000"

else

Field

endif

As for the character piece, can you use Data Cleanse to remove characters you dont want in that field?

[Paul-Evans · Accepted solution]

PadRight(PadLeft([prod_no],5,"0"),8,"0")

[MarqueeCrew · Accepted solution]

@Kavya432,

I hear lots of answers coming in.  Are they all solutions?  Here's my K.I.S.S. solution:

PadLeft(ToString([prod_no])+"000", 8, "0")

Cheers,

Mark 

[ChrisTX]

For your last question....I have Characters and alpha numeric characters in it. How I can filter those out.

Try a filter with the IsNumber function

[Kavya432]

THIS IS ONLY IF LENGTH OF PROD_NO <= 5.

THIS FORMULA IS APPLYING TO ALL PROD_NO

[Kavya432]

I tried to use this formula

if Length([Prod_no_new])<=5 Then PadRight(PadLeft([Prod_no_new],5,"0"),8,"0") Else [Prod_no_new] endif

but its only padding to the right, not padding to the left. I just quickly validated data. Could someone help to correct the formula

[DanielG]

DId you try mine?  That should work.  Not as slick as the others, perhaps, but functionally it should get you what you want.

[DanielG]

Plus @MarqueeCrew has the correct formula.  No need to try and put that into an if statement.

Just run it as a standalone formula on the field and you will get the results you want.

Much simpler than my wannabe Excel formula.  haha.