# Alteryx Designer Discussions

###### ALTER.NEXT:

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
SOLVED

## Length of fields

Highlighted
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

Highlighted
Alteryx Certified Partner

Hi @Kavya432 ,

Attached is an example showing how to do it.

Let me know if this works for you.

BEst,

Fernando Vizcaino

Highlighted
8 - Asteroid

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?

Highlighted
8 - Asteroid

Highlighted
Alteryx Certified Partner

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 reboot. Order shall return.
Highlighted
12 - Quasar

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

Highlighted
8 - Asteroid

THIS IS ONLY IF LENGTH OF PROD_NO <= 5.

THIS FORMULA IS APPLYING TO ALL PROD_NO

Highlighted
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

Highlighted
8 - Asteroid

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

Highlighted
8 - Asteroid

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