I have a column with integer numbers like this: `1, 2, 3, 4, 5` and I want to create another column with these values padded to length of three like this: `001, 002, 003, 004, 005`.

I wasn't being able to find a standard function for this.

• This 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. Feb 22, 2016 at 14:20
• use \0\00 on custom number format
– John
Dec 11, 2019 at 0:53

Recommended way is to use TEXT() function.

Quick summary on how you would use it in your case:

``````=TEXT(5,"000")
``````

Would output:

``````005
``````

Of course you would substitute the literal 5 with a reference to another cell where the source number is.

• Would there be a way of padding with spaces?
– Álex
Apr 14, 2016 at 8:47
• I think just substitude "000" with " " above Jun 15, 2016 at 20:44

If you just want display changes you can apply the custom number format "000" to the cells. Select the cells, Click on Format > Number > More Formats > Custom number format....

From Docs editors help:

A digit in the number. An insignificant 0 will appear in the results.

• Can you edit your answer to expand on how the OP would do this? May 9, 2016 at 21:55

# 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

``````-------------
| 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)
``````
• Perfect! This is the best answer and it works with non-numeric values ;-D Dec 17, 2017 at 21:33