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 :]– J20Commented Nov 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
-
This method allows you to continue to use them as numbers Commented Feb 14, 2020 at 19:20
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 Commented May 17, 2016 at 16:57
-
@davea0511 That is kind of the point - to display the text
050, not any interpretation of it. Commented 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!– StalinkoCommented Nov 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?– aleCommented Jan 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. Commented Jun 22, 2016 at 18:00