Pad numbers to specific length in Google Spreadsheet

Question

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.

Is it possible to pad string to length in Google Spreadsheet?

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

Answer 1

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.

Answer 2

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.

Answer 3

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)