H
haile
Several threads deal with the suppression of erroneous auto-formatting of
long number strings into scientific notation. The workarounds suffice for
trivial cases, but users need 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 in
Excel 2007. Scientific notation is retained, even though the cells are
formatted as text.
Manually adding apostrophes where required does appear to work, but would
take many hours for each column. The manual approach is not feasible in
cases where large row sets must be handled frequently.
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 desired formatting is retained
(that is, the scientific notation is gone), even though the contents and
formatting are visually indistinguishable from the original.
Although the last method works, it is difficult for users - particularly
because the old "paste/special" functions are not easily accessible in the
new Excel GUI. Furthermore, it is a method I found by trial and error; I am
at a loss to explain to users why it works (and how they can reuse the method
for general problem solving), because I do not understand why it works
myself.
Text should be text. Formatting behavior should be predictable, based on
the visual artifacts specific to the data that is being displayed. Nothing
so simple should be this hard. Does anyone know an easier way?
Cheers.
long number strings into scientific notation. The workarounds suffice for
trivial cases, but users need 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 in
Excel 2007. Scientific notation is retained, even though the cells are
formatted as text.
Manually adding apostrophes where required does appear to work, but would
take many hours for each column. The manual approach is not feasible in
cases where large row sets must be handled frequently.
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]
way to reach this feature in Excel 2007, the desired formatting is retained
(that is, the scientific notation is gone), even though the contents and
formatting are visually indistinguishable from the original.
Although the last method works, it is difficult for users - particularly
because the old "paste/special" functions are not easily accessible in the
new Excel GUI. Furthermore, it is a method I found by trial and error; I am
at a loss to explain to users why it works (and how they can reuse the method
for general problem solving), because I do not understand why it works
myself.
Text should be text. Formatting behavior should be predictable, based on
the visual artifacts specific to the data that is being displayed. Nothing
so simple should be this hard. Does anyone know an easier way?
Cheers.