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)
2This is not precisely what you have asked for, but achieves a comparable effect and might be a solution for a common task people search for: (1) select the column with the numbers you want padded. (2) Select Format > Number > More Formats > Custom Number Format ... (3) In the dialog enter as many 0 as digits the number string should have, e.g. 000 in your example. – tophcito – 2016-02-22T14:20:42.373
2
It should not simply be closed, it should be moved to http://webapps.stackexchange.com/
– Lennart Rolland – 2017-01-14T14:10:18.503use \0\00 on custom number format – John – 2019-12-11T00:53:26.053