If I enter a number beginning with 0 such as 01234 unless I put a ' in front it drops the 0 when I press enter. Is there a way of stopping it doing this other than entering a ' each time?
Replies (6)
Please login or register to join the discussion.
The suggested solution
may be undesirable if you still want the number to retain its apparent numerical value for computational purposes. That seems unlikely in most cases where you want the leading 0 to appear, but it is possible. For that you would have to play around with the custom number formats, or else reserve another column, perhaps hidden, which returns the numerical value of the displayed text column, using VALUE()
With kind regards
Clint Westwood
Leading zero
In Open Office and excel - simply putting an apostrophe in front of the number makes it retain the leading zero and still acts as a number. The ' does not show on the spreadsheet.
You can find more about this in help.
Text, numbers and coercion
Just to expand on the last comment about 'numbers' preceded by an apostrophe still acting as numbers. In Excel at least, this is only true where Excel uses 'coercion' to force text to be treated as a number if it only contains valid numeric characters. This happens when Excel 'thinks' you want it to be a number e.g. if you entered something like =A1+A2. On the other hand, if you were to enter =SUM(A1:A2), SUM() is designed to ignore text so it will result in zero.
Or...
This works too:
Open "Number" tab of format cells dialogue box, and format cells as "user defined", and assign
format code #0General in the box at the bottom of the dialogue box.
This adds a single leading zero to any number, including decimals without the need for adding extra ', formatting as text. or even typing the desired leading zero.