Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Concatinate Numbers field to 6 digits ( add 0s )

Topic Labels: Formulas
Solved
Jump to Solution
1671 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Jim_DiGiovanni
5 - Automation Enthusiast
5 - Automation Enthusiast

This is a newbie question.

I have a sequence of numbers in my inventory and these are not auto generated, so I am either working with Number or Formula here.

I am trying to add zeros to a sequence so that all numbers have 6 digits. Currently, my numbers start at 0 and go up to 4 digits, I think I am looking for an If / Then LEN formula, but I can’t figure out the syntax to make it work.

For example, I need 5 to read as 000005, and I need 1401 to read 001401. etc.

Thanks in advance.

Screen Shot 2022-11-14 at 1.24.58 PM

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

Even simpler than that:

RIGHT("000000" & {Merch Inventory...}, 6)

See Solution in Thread

3 Replies 3
Kamille_Parks
16 - Uranus
16 - Uranus

Even simpler than that:

RIGHT("000000" & {Merch Inventory...}, 6)

Fantastic! That works perfectly. Thank you. Can you explain a little more about what the RIGHT command is? I thought it would be a LEN command. But I am really a novice.

RIGHT() says “return the last [#] characters at the end of a string”. There is also the “cousin” function LEFT() which does the same thing except its “return the first [#] characters”.

LEN() returns “how many characters are in a string”. It also only works on strings so you would have to force your numbers to be strings by doing #&"". So a LEN solution would be something like

IF(
LEN({Merch Inventory...}&"") = 1,
"00000" & {Merch Inventory...},
IF(
LEN({Merch Inventory...}&"") = 2,
"0000" & {Merch Inventory...},
IF(
LEN({Merch Inventory...}&"") = 3,
"000" & {Merch Inventory...},
IF(
LEN({Merch Inventory...}&"") = 4,
"00" & {Merch Inventory...},
IF(
LEN({Merch Inventory...}&"") = 5,
"0" & {Merch Inventory...},
IF(
LEN({Merch Inventory...}&"") = 6,
{Merch Inventory...}
))))))