Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can General format numbers be mistaken as text?
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can General format numbers be mistaken as text?
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. -- Biff Microsoft Excel MVP "Treehugger" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can General format numbers be mistaken as text?
Thank you for your answer and prompt response.
"T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Treehugger" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can General format numbers be mistaken as text?
You're welcome!
-- Biff Microsoft Excel MVP "Treehugger" wrote in message ... Thank you for your answer and prompt response. "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Treehugger" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can General format numbers be mistaken as text?
"Treehugger" wrote:
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" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can General format numbers be mistaken as text?
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" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can General format numbers be mistaken as text?
"Treehugger" wrote:
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 ----- "Treehugger" wrote in message ... 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup return NA for numbers format as general | Excel Worksheet Functions | |||
Format: General - Text - General | Excel Worksheet Functions | |||
excel numbers in general format i cant add cant change format | Excel Worksheet Functions | |||
Change CSV-load cell format from GENERAL to TEXT for numbers? | Excel Discussion (Misc queries) | |||
typing mistaken for date format | Excel Worksheet Functions |