We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.
alteryx Community

# Alteryx Designer Desktop Discussions

SOLVED

## Length of fields

8 - Asteroid

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

18 REPLIES 18
17 - Castor

Hi @Kavya432 ,

Attached is an example showing how to do it.

Let me know if this works for you.

BEst,

Fernando Vizcaino

12 - Quasar

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?

9 - Comet

20 - Arcturus

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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
15 - Aurora

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

8 - Asteroid

THIS IS ONLY IF LENGTH OF PROD_NO <= 5.

THIS FORMULA IS APPLYING TO ALL PROD_NO

8 - Asteroid

I tried to use this formula

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

12 - Quasar

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

12 - Quasar

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.

Labels