A table in Excel (97 SR1) has a column with departmental codes: 01, 02
...50. Sorting on that column puts the departments in order 10, 11, ...
50, 01, 02. Why are the zeros (not oh's, I checked) at the end?
The reason relates to the cell formats. Typing "05" causes a default to
a number and the leading zero is not displayed. Hence the typist had opted
for left-justified text format, and the "numbers" appear as described.
In an experiment on this, I managed inadvertently to make some cells in
the column as text while others remained as numbers. If a decimal place
was specified, that meant that one cell showed as 10.0 while another
stayed as 10. Removing the decimal and sorting that column resulted in a
column with 10 at the top and 10 at the bottom.
Is this a bug (should I look for SR2?) or normal behaviour of Excel? It
does not inspire confidence.
The pragmatic solution was to copy the values to a new column, using
VALUE() and formatting the new column as CUSTOM(00000). The new values
showed as right-justified but with leading zeros. This does, however,
put the lie to a remark in Excel's Help (under text functions) "You do
not generally need to use the VALUE function in a formula because
Microsoft Excel automatically converts text to numbers as necessary. This
function is provided for compatibility with other spreadsheet programs."
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
|