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

Data Investigation

Accepted answers

Paul-Evans

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

MarqueeCrew

@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

All comments

fmvizcaino

Hi @Kavya432 ,

Attached is an example showing how to do it.

Let me know if this works for you.

BEst,

Fernando Vizcaino

2020-02-10 Length of fields.yxmd

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

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

MarqueeCrew

@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

Kavya432

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335