36

I have a column in which all inputs are numbers. But these numbers are actually not real numbers. Not real in the sense, 050 in this case is very different from 50

The problem is that whenever I add 050 the sheet automatically turns it to 50. I have also tried ** 050**. That is, prepending a space to no avail. It still auto-corrects as 50.

What can I do to avoid this?

46

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.

  • 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
33

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.

  • 1
    Yes, this is in the "new" Google Spreadsheet. – MrWhite Nov 20 '14 at 1:08
7

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"
  • 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. – Vidar S. Ramdal 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()))
-2

You can format the contents of the cell as Plain Text. Here are step-by-step instructions of how to format a number in a Google Sheet.

  • 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. – Ricky Clarkson Jun 22 '16 at 18:00

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