Excel – Turn off scientific notation in Excel

If you wish Excel to keep the cell formatted exactly as entered, i.e. 8230e12, regardless of whether you are looking at it or editing it, then this can't be done whilst retaining the ability to treat the cell contents as a number.

The only way I can see round this is to enter your data in cells that are formatted as text, and then have another cell, formatted as some kind of number, that has a formula of =VALUE(A1) or whatever so that calculations can be performed on this cell.

Other than that you are looking at some VBA to manage this, overkill I would have thought.

You can, but it takes a couple of manual steps. These steps are from Excel 2003, but I'd assume the wizard is similar in 2007:

1. Rename the file as TXT. Don't double-click it.
2. Open Excel
3. Click File, Open
4. Locate and double-click the text file
5. It should open a "Text Import Wizard"
6. In step one, choose "Delimited"
7. In step two, uncheck "tab" and check "comma"
8. In step three, scroll-over to the last column, shift-click to select all columns, then click "Text" as the format.
9. Ignore the annoying "Number stored as text" warnings everywhere

Goes pretty fast with some practice.