Length of fields
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Data Investigation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Kavya432 ,
Attached is an example showing how to do it.
Let me know if this works for you.
BEst,
Fernando Vizcaino
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
PadRight(PadLeft([prod_no],5,"0"),8,"0")
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
THIS IS ONLY IF LENGTH OF PROD_NO <= 5.
THIS FORMULA IS APPLYING TO ALL PROD_NO
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
DId you try mine? That should work. Not as slick as the others, perhaps, but functionally it should get you what you want.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.