Help

Re: Concatinate Numbers field to 6 digits ( add 0s )

Solved
Jump to Solution
1391 2
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...}
))))))