Temporary solution
Here's the temporary workaround that I came up with.
Working formula
Just use this formula:
`=IF(LEN(A2)<3, CONCATENATE(REPT("0", 3-LEN(A2)), A2), A2)`
Replace 3
with padding length and 0
with padding character. A2
is a padding source.
Explanation
Consider the following spreadsheet:
-------------
| A | B |
-------------
| 1 | 001 |
-------------
| 2 | 002 |
-------------
| 31 | 031 |
-------------
| 45 | 045 |
-------------
| 500 | 500 |
-------------
We have initial column (A) with integers that we want to pad. The (B) column will contain the special formula to process the data.
Concatenate!
First of all we need a way to concatenate value from A
column with padding string. We can do this with CONCATENATE
function:
=CONCATENATE("00", A2)
Repeat!
Right now, padding character 0
is repeated twice in our formula. This is not good. We can use REPT
function to repeat our padding character several times like this:
=REPT("0", 2)
This formula will repeat 0
two times. Let's combine them:
=CONCATENATE(REPT("0", 2), A2)
Calculate length!
That's better. But padding length has a constant value in our formula and that is not going to work with numbers greater than 9. We can fix this by calculating length of the padded string (using LEN
function) and subtract it from our target length:
=3-LEN(A2)
Let's add it to our formula:
=CONCATENATE(REPT("0", 3-LEN(A2)), A2)
However, we will get negative results for values greater than 999 and it will break the REPT
function (number of repetitions can not be negative).
We can easily fix it by adding a condition:
=IF(LEN(A2)<3, "APPLY REPT", "OUTPUT AS IS")
Let's put the final peaces together:
=IF(LEN(A2)<3, CONCATENATE(REPT("0", 3-LEN(A2)), A2), A2)