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
Are you using a string data type for the output? A numeric type will drop leading zeros.
- 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
Its working with the formula. But when I tried to copy to excel some how the zero's to the left side of Prod_no is disappearing(do we need to change any datatype?).
Thanks all for the solutions!
Other quick question if Base_Prod_No has zero's or alpha numeric characters or if has seven characters is there any way we can replace with blank?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
forgot to attach the data file
Other quick question if Base_Prod_No has zero's or alpha numeric characters or if has seven characters is there any way we can replace with blank?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Its not working if I use the IsNumber([Base_Prod_no]), its not bringing any data trough.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
It has to be a string to retain the lead zeros.
A numeric field will drop those as they are not relevant when interpreting it as a number.
I tested by creating a text file with this in it:
test *
1 0000111100000
2 11110000
3 0101010
I used Output tool to drop it was an Excel to my desktop, and it retained the string structure.
You just need to make sure it is a string before you push it down to its final location.
If you copy and paste it from the results window to Excel, it will be translated into a number. So dont do that. 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
use to be clear on my last post, the 1,2,3 at the front of each item is the row# not the lead digit of what I was testing...
Didnt realize how bad that looked when I typed and posted it initially...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
For your second question:
I am sure there is a more elegant way to do it, but perhaps a formula on that column with "if contains(field, "0") then "" else y endif (with additional layers if needed)
Please note that is above is roughly written and will require syntax tweaking to make it accurate.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
No Problem, will try to output to excel.
Could you please help me to remove any alpha numeric and zeros and just leave a empty or blank field in Base_Prod_no.
- « Previous
-
- 1
- 2
- Next »