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?
Add a comment
|
6 Answers
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
-
1Yeah, I realised that posting as a comment would leave the question showing as 'unanswered' so best to do it properly :]– J20Nov 16, 2012 at 8:30
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
-
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"
answered Jan 16, 2014 at 15:33
-
Does not work if it is a formula, as this will display the formula, not the result of the formula May 17, 2016 at 16:57
-
@davea0511 That is kind of the point - to display the text
050, not any interpretation of it. May 18, 2016 at 7:16 -
This is the only solution without changing the properties of the cells manually. Exactly what I needed! Thank you!– StalinkoNov 2, 2017 at 19:15
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()))
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
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.
-
1Why can't you put the instructions here in this answer? What happens when that link stops working?– aleJan 20, 2015 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, 2016 at 18:00