3E281 is an ID in a text file I’m working with. When I import this into MS Excel it converts it to “3.00E+281” and formats the cell as scientific.
If I go back and change the cell to general or text and retype 3E281 it converts it again.
Just for giggles I started a brank new worksheet and typed 3E281 into a cell. Sure enough it was converted into 3.00E+281.
What gives?
If there a way to stop this from happening?
What version of Excel are you using? I just tried it, changing the cell format to “text” before entering the data, and it worked just fine.
Put an apostrophe before it ('3E281)and Excel will read it as text.
Typing a space before it will also work. But I like Q’s way better.
It is Excel 2002
It is interesting because this is an ID mixed in with a handful of others as a teb-delimited text file. I can’t figure out a way to stop this from happening when I open the file straight into Excel.
If I create a new worksheet, change the cell to text and enter the data, it does work fine. The problem is I’m not hand entering this stuff.
What causes this? What does Excel think this is?
Excel sees it as a number in scientific notation.
“number"E"number” is a abbreviation for "number10^“number”. In your case 3E281 is read as 310^281.
I just tried this with a large tab-delimited text file. Upon opening the file, the Text Import wizard starts. On the third page of the wizard–where you set the format of each column, highlight the column containing the IDs and change the format type to “text” using the “Column Format” radio buttons in the upper right-hand corner. All data in this column will then be treated as text. If you leave it as “general”, 3E281 is indeed stored as the number “3E+218”.
“3E+218” is shorthand for the numeral “3 x 10^218”, the value represented by the digit 3 followed by 218 zeroes. Many computer programs store scientific numerical data in this way–using “E” to stand in to the multiplication by an exponent–since it is difficult for ASCII test to show exponents.
Scientific notation is a compact numerical way to handle very large or very small numbers, which come up quite commonly in scientific work.
I just tried this with a large tab-delimited text file. Upon opening the file, the Text Import wizard starts. On the third page of the wizard–where you set the format of each column, highlight the column containing the IDs and change the format type to “text” using the “Column Format” radio buttons in the upper right-hand corner. All data in this column will then be treated as text. If you leave it as “general”, 3E281 is indeed stored as the number “3E+218”.
“3E+218” is shorthand for the numeral “3 x 10^218”, the value represented by the digit 3 followed by 218 zeroes. Many computer programs store scientific numerical data in this way–using “E” to stand in to the multiplication by an exponent–since it is difficult for ASCII test to show exponents.
Scientific notation is a compact way to handle very large or very small numbers, which come up quite commonly in scientific work.
Excel has it’s roots in accounting, so it assumes that any valid number entered into a cell is meant to be a number. You have to delineate the entry as text with the apostrophe, or overall formatting as CJJ* noted.
My copy of Excel just opens csv files without the option of importing.
I think I’ll just avoid Excel all together and process this data in Access.
Thanks for the help all.
Here’s something I just noticed. We have another ID that is 5E728 but it doesn’t format this one as scientific.
After a bit of playing it seems the second number after E has to be below 308 for Excel to auto-format the cell as scientific.
Yes, but if you open a blank sheet and then use the import function, you will get the options that CJJ* pointed out. Furthermore, there will be a button on the toolbar the allows you to ‘Update’ your data. Forever more you would be able to import your data at the click of a botton, do whatever it is you need to do to it, and then save it off to a file. No muss, no fuss.
I had this problem before, with Excel formatting certain serial numbers as dates. If you use the import function, and designate your columns to be text, it will work ok.
Checking the document I wrote up about it (our clients have to do this sometimes), you use Data->Get External Data -> Import Text File, make sure your delimiters are set right, and then force it to import the problem columns as text.
1.7976931348623157 x 10[sup]308[/sup] is the upper limit of double-precision floating point numbers.
Excel stores numbers as double-precision floating point. The upper limit is 1.7976931348623157 x 10[sup]308[/sup].
Rats. How the heck did that happen?