|Subject:||Formatting Numbers as text|
|Posted by:||John (jkrae…@NOprincetonSPAM.edu)|
|Date:||Wed, 5 May 2004|
We are having a problem with numbers stored as text in Excel 2002.
If a cell is explicitly formatted as text, and a number is inserted; then
automatic error checking inserts a comment indicating that the number is
stored as text and offering to fix it. The fact that this is not an error
and the field is actually a text field containing numeric information is
immaterial to Excel, it wants to fix the problem for us. On a bright note,
however, is that it recognizes the field as text.
If a cell is not explicitly formatted as text, and a number is inserted;
then Excel assumes that it is a number. If one then selects the cell and,
via the Format Cells dialog, set the format explicitly to text, the number
assumes the left-justified position that would indicate it is text, but
automatic error checking does not indicate an error. It appears that Excel
is still treating this as a numeric value, since, when we try to move it
into a database we get a type error.
If one then puts a leading apostrophe in the field, automatic error checking
suddenly wakes up and realizes the field is a number stored as text, and it
is treated as text.
This cannot be by design.
Has anyone else run in to this behaviour?