39

I have a column comprised exclusively of numbers. Whenever I add 050, the sheet automatically changes it to 50. I have tried prepending a space, but it makes no difference.

What can I do to avoid this?

0
47

If there's a difference between 50 and 050, what you have are strings (i.e. text).

Changing the cell format to Plain Text via Format>Numbers>Plain Text will keep leading zeros.

2
  • 1
    was wondering why you did not post it as an answer :)
    – naveen
    Nov 16 '12 at 8:29
  • 1
    Yeah, I realised that posting as a comment would leave the question showing as 'unanswered' so best to do it properly :]
    – J20
    Nov 16 '12 at 8:30
35

Since @jam 's answer there might be some improvement in Spreadsheet's features.

If you'd like to preserve and use the numbers as figures (e.g., automatic fill) you might apply the settings from the

Format > Number > More Formats > Custom number format...

menu, and set the pattern to 000, where the number of zeros represent the number of digits to which the completion will be made with the leading zeros.

2
  • 1
    Yes, this is in the "new" Google Spreadsheet.
    – MrWhite
    Nov 20 '14 at 1:08
  • This method allows you to continue to use them as numbers
    – simpleuser
    Feb 14 '20 at 19:20
8

A quicker way of formatting a cell as text, is to add a preceding apostrophe to your number. In a cell, type

'050

and it will be interpreted as the string 050.

Another option is to write it as a formula:

="050"
3
  • Does not work if it is a formula, as this will display the formula, not the result of the formula
    – davea0511
    May 17 '16 at 16:57
  • @davea0511 That is kind of the point - to display the text 050, not any interpretation of it. May 18 '16 at 7:16
  • This is the only solution without changing the properties of the cells manually. Exactly what I needed! Thank you!
    – Stalinko
    Nov 2 '17 at 19:15
2

I needed to have a leading zero on the current month of the year for creating a custom date code I use on my invoices. This is what I did:

=IF(LEN(MONTH(TODAY()))=1;CONCATENATE(0,MONTH(TODAY()));MONTH(TODAY()))
0

If you came here looking for a way to add a leading zero to a time cell, select "Custom number format" from the dropdown and set this value:

hh":"mm":"ss

-2

You can format the contents of the cell as Plain Text.

An external link failed. Answer as suggested in a comment by @RickyClarkson:

  • Select the cell(s),
  • Go to Format -> Number -> Plain Text.
2
  • 1
    Why can't you put the instructions here in this answer? What happens when that link stops working?
    – ale
    Jan 20 '15 at 3:16
  • It has now stopped working. It looks like you need to select the cell(s), then go to Format -> Number -> Plain Text. Jun 22 '16 at 18:00

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