Can General format numbers be mistaken as text?

[Treehugger]

My spreadsheet has several data input cells. The data is later used in a
calculation. I don't want the input restricted by format or number of
decimal points. I want the spreadsheet to display whatever the user types, so
I used the General format. A co-worker is concerned that Excel may misread
the numbers as text values unless they are specifically formatted as numbers.
Is this possible?

 

[T. Valko]

Is this possible?

No. By default all cells have a General format. This can be described as
basically "what you see is what you get". If you type in a number, it's a
number. If you type in text, it's text. In general, a lot of people cause
their own problems by "over formatting". They seem to think that if they
enter numbers in a cell then they have to format the cell as Number. Same
thing for text. This is not necessary.

 

[Treehugger]

Thank you for your answer and prompt response.

No. By default all cells have a General format. This can be described as
basically "what you see is what you get". If you type in a number, it's a
number. If you type in text, it's text. In general, a lot of people cause
their own problems by "over formatting". They seem to think that if they
enter numbers in a cell then they have to format the cell as Number. Same
thing for text. This is not necessary.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thank you for your answer and prompt response.

 

[JoeU2004]

I want the spreadsheet to display whatever the user types, so
I used the General format. A co-worker is concerned that Excel
may misread the numbers as text values

That depends on your coworker's definition of "number". Moreover, although
the General format will not treat bona fide numbers as text, that does not
mean it will "display whatever the user types" exactly as it is entered.

If you enter a Social Security "number" as 123-45-6789, the General format
will indeed treat that as text, as it should. But then again, so will a
cell with any explicit number format. So perhaps we can ass-u-me that is
not how your coworker defines "number".

However, there are many instances where the content of a General cell is not
displayed as it was entered. And perhaps more insidiously, there are many
instances where the format of a General cell is changed automagically to an
explicit number, and that can adversely affect the display of subsequent
data entry.

First, a General cell will not display leading zeros of any number. For
example, if you enter 000123, the General cell will display simply 123.
Likewise, trailing zeros of a decimal fraction are not displayed in a
General cell. If you enter 1.2300, it will be displayed as 1.23.

Second, it appears that the General format changes the display of numbers
that have more than 11 characters. For example, 123456789012 is displayed
as 1.234567E+11, and the column width may be changed (if it had not been
changed from the default), although the actual value remains 123456789012.
I wonder if this is what your coworker calls "text", not understanding the
Scientific format.

If the column width has been changed from the default, or if the number has
a decimal fraction, the display of numbers with more than 11 characters is
rounded to fit the column width, up to 11 characters.

(Even worse: for numbers with more than 15 significant digits, not only are
they displayed with Scientific format, but also digits after the 15th are
replaced with zero; thus, the actual value is changed. All number formats
work the same way. But such "numbers" are typically not intended to be
treated as such -- for example, product "numbers". They should be entered
as text in one way or another.)

Finally, if a cell has a General format, and the number is entered with
commas, a dollar sign or a percent sign (and that list might not be
exhaustive), the cell format is changed to Number with Use Separator,
Currency, or Percentage automatically. The display of any number entered
subsequently will be changed to modified format.

So if you want control over the appearance of numbers and the column
width -- and usually we do -- you need to choose an appropriate number
format. For example, in financial worksheet, we usually want 12 dollars and
20 cents displayed as 12.20, not 12.2.

One final note.... If a General cell contains a number and the cell format
is changed to Text one way or another, TYPE(cell) still returns 1 (number)
until you edit the cell (e.g. append a digit, or simply press F2), after
which TYPE(cell) returns 2. Although that does not sound like the
coworker's concern literally, I wonder if there is some miscommunication,
and this is indeed what the coworker is talking about. It causes such
mysterious side-effects as: SUM(cell) returns the numeric value initially,
but it returns zero after the "number" is modified. Thus, Excel "misreads a
number as text", but only sometimes.


----- original message -----

 

[Treehugger]

Thanks for the detailed reply. The cells in question should not be populated
with anything other than bona-fide numbers, so I'm less concerned about
someone entering text, dates, currency, etc. I do however want to preserve
numbers that are entered as whole numbers or numbers with digits after the
decimal point, no matter how many digits that may be. That's why I'm
reluctant to use the number format since it requires you to select a certain
number of digits following the decimal point.

I believe that his concern is that without an explicit "Number" format,
Excel may simply choose to treat 1234 as a number or text, and that would
impact the subsequent calculations. As long as 1234 or 1.234 is always read
as a number, I'm happy.

 

[JoeU2004]

I do however want to preserve numbers that are entered as whole
numbers or numbers with digits after the decimal point, no matter
how many digits that may be. That's why I'm reluctant to use the
number format since it requires you to select a certain number of
digits following the decimal point.

It's fine if you want to use General format. But I want to clear up one
misconception that you have.

The number format that you choose affects only how values appear (are
displayed), not how they are "preserved" (actual value).

For example, if you choose the format Number with 2 decimal places, and you
enter 1.23456789, the full value (1.23456789) will be preserved even though
Excel displays only 1.23, per your request. And you can enter numbers with
fewer decimal places, e.g. 12345.

The same is true with the General format if you enter a number with more
than 11 characters. For example, if you enter 1.2345678901, it might be
displayed as 1.234568 (default column width), but the full value
(1.2345678901) will be preserved.

I believe that his concern is that without an explicit "Number" format,
Excel may simply choose to treat 1234 as a number or text, and that
would impact the subsequent calculations.

Based on what: a random variable? My point is: obviously that
statement is incorrect or incomplete.

Perhaps your coworker is worried that there may be situations where Excel
will change the cell format from General to Text automagically, just as it
changes from General to Number, Currency or Percentage. Thereafter, any
numbers entered into the cell will indeed be treated as text.

I know of one situation when that happens: entering an arithmetic formula
that refers a Text cell with a numeric string (e.g. =1+A1). But that
conversion to Text format seems to happen even if the cell with the formula
has an explicit number format.

(I am surprised by the conversion to Text format. I thought references like
1+A1 converted numeric strings to numbers. That is certainly true when 1+A1
is used as an operand in a compare operation.)

Anyway, I'm digressing.... You are taking your coworker's words literally
and presume that he is talking about entering only constants. I agree with
Biff: I have not found an instance where entering a numeric constant into a
General cell causes the cell or the constant to be treated as text.


----- original message -----