3 Replies Latest reply on Jun 14, 2016 9:25 AM by Joshua Milligan

Period on end of value when zero padding using STR/IF functions

I have a use case this week where I'm receiving vendor UPC's of varying lengths, but they need to be space padded to a consistent 13-digit length. So I wrote an IF statement based upon the fields I receive to convert them to this 'proper' format. Instead of the correct padding, i'm getting 1 too few leading 0's and getting a period on the end instead.

My Calculation for Vendor UPC is as follows:

if len(str([Ven UPC]))=12 then "0"+Str([Ven UPC])

elseif len(str([Ven UPC]))=11 then "00"+Str([Ven UPC])

elseif len(str([Ven UPC]))=10 then "000"+Str([Ven UPC])

elseif len(str([Ven UPC]))=9 then "0000"+Str([Ven UPC])

elseif len(str([Ven UPC]))=8 then "00000"+Str([Ven UPC])

elseif len(str([Ven UPC]))=7 then "000000"+Str([Ven UPC])

elseif len(str([Ven UPC]))=6 then "0000000"+Str([Ven UPC])

elseif len(str([Ven UPC]))=5 then "00000000"+Str([Ven UPC])

elseif len(str([Ven UPC]))=4 then "000000000"+Str([Ven UPC])

elseif len(str([Ven UPC]))=3 then "0000000000"+Str([Ven UPC])

elseif len(str([Ven UPC]))=2 then "00000000000"+Str([Ven UPC])

elseif len(str([Ven UPC]))=1 then "000000000000"+Str([Ven UPC])

ELSE str([Ven UPC])

END

I've attached a screenshot of what i'm seeing (where Calculation1 corresponds to the LEN of the initial field Ven UPC). Has anyone seen this behavior...or more realistically, am I missing something simple? Using Desktop 9.01(corp standard) FYI. Thanks in advance!

• 1. Re: Period on end of value when zero padding using STR/IF functions

Jeremy,

I'm guessing the period is the decimal point that is somehow getting caught with the STR() function.

How about a calculation like this:

RIGHT("00000000000000" + Str([Ven UPC]), 13)

and if needed, you can get rid of the period with:

REPLACE(RIGHT("00000000000000" + Str([Ven UPC]), 13), ".", "")

Hope that helps!

Best Regards,

Joshua

1 of 1 people found this helpful
• 2. Re: Period on end of value when zero padding using STR/IF functions

That's perfect. I certainly took an overly complex approach to this calc. Really appreciate you taking a look!

• 3. Re: Period on end of value when zero padding using STR/IF functions

You're welcome!

I also realized, you would want to strip the period first, so the length is right:

RIGHT("00000000000000" + REPLACE(Str([Ven UPC]), ".", ""), 13)

-Joshua

1 of 1 people found this helpful