Formatting Numbers as text

Giganews Newsgroups
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?

Thanks,
    John

Replies