Suggestions by responders suffice in trivial cases, but no one has suggested
a general purpose solution, for example when scientific notation is
intermittently applied on a column containing thousands of rows.
Highlighting the entire column and formatting it as Text does not work.
Manually adding apostrophes where required does appear to work, but would
take many hours for each column.
My attempt at a general purpose solution is to use a contenate formula as
follows:
Cell A1: TEXT "273074206023", displayed as "2.73074E+11"
Cell A2: =CONCATENATE("'",a1)
This does not work. The leading apostrophe displays in the cell.
Next attempt:
Cell A1: TEXT "273074206023", displayed as "2.73074E+11"
Cell A2: =Trim(CONCATENATE(" ",a1))
This WORKS. Furthermore, if we copy/paste special/Value back to the
original cell (using the legacy key sequence [ALT], as I know of no other
way to reach this feature in Excel 2007, the desire formatting is retained
(that is, the scientific notation is gone), even though the contents and
formatting are visually indistinguishable from the original.
If someone can tell me why the last method appears to work, and how to
circumvent the auto-formatting by a more straightforward means, I will be
nudged a little bit closer to recommending an upgrade to Excel 2007 for
several thousand current users of Excel 2003.
Cheers.