30

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.

closed as off-topic by DavidPostill, fixer1234, RedGrittyBrick, VL-80, Heptite May 14 '16 at 2:47

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "This question is not about computer hardware or software, within the scope defined in the help center." – DavidPostill, fixer1234, RedGrittyBrick, VL-80, Heptite
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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 '16 at 14:20
  • 2
    It should not simply be closed, it should be moved to webapps.stackexchange.com – Lennart Rolland Jan 14 '17 at 14:10
48

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 '16 at 8:47
  • I think just substitude "000" with " " above – Lennart Rolland Jun 15 '16 at 20:44
11

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.

  • 2
    Can you edit your answer to expand on how the OP would do this? – Burgi May 9 '16 at 21:55
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)
  • Perfect! This is the best answer and it works with non-numeric values ;-D – t3chb0t Dec 17 '17 at 21:33

Not the answer you're looking for? Browse other questions tagged or ask your own question.