I have numbers of varying length in a field. All values need to be 7 characters long and need zeros appended to left of existing number. So 41449 should be 0041449. I have tried the following (based on other posts) and for some reason it won't add the zeros. * First attempt:* Using formula tool created a new field called "code_length" to determine how many zeros are needed (set data type to double): 7 - Length([Field-1]) * Using formula tool added this formula: If Length([Field-1) = 7 then [Field-1] else PadLeft([Field-1], [code_length], "0") endif * Confirmed format of field in Excel source was General. I also tried Text. Neither changed the result. * Confirmed field is importing as string in Alteryx. Tried each string type with no success. * Second attempt:* Replaced the conditional statement with the following (to test whether PadLeft working at all): PadLeft([Field-1], 2, "0") * This did not work. * Third attempt:* Tried creating a new Excel source to clear out any formatting I might be missing. * This did not work. I am not sure what I am doing wrong. Can anyone help? Thanks.

Padleft not working

JBO

I have numbers of varying length in a field.

All values need to be 7 characters long and need zeros appended to left of existing number. So 41449 should be 0041449.

I have tried the following (based on other posts) and for some reason it won't add the zeros.

First attempt:
- Using formula tool created a new field called "code_length" to determine how many zeros are needed (set data type to double): 7 - Length([Field-1])
- Using formula tool added this formula: If Length([Field-1) = 7 then [Field-1] else PadLeft([Field-1], [code_length], "0") endif
- Confirmed format of field in Excel source was General. I also tried Text. Neither changed the result.
- Confirmed field is importing as string in Alteryx. Tried each string type with no success.
Second attempt:
- Replaced the conditional statement with the following (to test whether PadLeft working at all): PadLeft([Field-1], 2, "0")
- This did not work.
Third attempt:
- Tried creating a new Excel source to clear out any formatting I might be missing.
- This did not work.

I am not sure what I am doing wrong. Can anyone help?

Thanks.

Preparation

Accepted answers

MarqueeCrew

Padleft(ToString([field]),7,"0")

Try it. You'll like it.

Yes! You have to store this as a string. Storing as an integer will erase the leading zeros.

All comments

MarqueeCrew

Padleft(ToString([field]),7,"0")

Try it. You'll like it.

Yes! You have to store this as a string. Storing as an integer will erase the leading zeros.

BenMoss

And make sure you set the field type to be strong, as you know the length, you can choose just string and put the length as 7.

If you use a double you'll immediately strip the 0's you just prefixed your value with!

JBO

Ack! @MarqueeCrew @BenMoss I feel foolish. Of course I over-complicated it. Thank you!

Hiblet

Also: PadLeft needs the numeric value to be the length of the whole final string. So, if you are putting 2 chars on at the start, the length needs to be the current length plus 2, ie "2+Length([Field])". Otherwise, PadLeft() does nothing and seems to not be working.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703