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
Solved! Go to Solution.
Hi @Kavya432 ,
Attached is an example showing how to do it.
Let me know if this works for you.
BEst,
Fernando Vizcaino
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?
PadRight(PadLeft([prod_no],5,"0"),8,"0")
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
THIS IS ONLY IF LENGTH OF PROD_NO <= 5.
THIS FORMULA IS APPLYING TO ALL PROD_NO
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
DId you try mine? That should work. Not as slick as the others, perhaps, but functionally it should get you what you want.
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.