52

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.

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

3 Answers 3

83

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.

4
  • 1
    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
  • 1
    @Álex : Use ? instead of 0 for whitespace padding. Source (See table under Custom Number Formatting pulldown.)
    – kando
    Jan 9 at 19:47
  • thank you, friend! the ? has eluded me and will change my life for the better
    – alex_jwb90
    Feb 1 at 16:11
12

If you just want display changes you can apply the custom number format "000" to the cells.enter image description here

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.

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

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

Not the answer you're looking for? Browse other questions tagged .