Cell format problem

[Guest]

We import results into Excel 2003 from third party software. One column is
populated with identifiers such as 3B2, 3C2, 3E4 etc. Any identifiers that
have an "E" in them convert to Scientific Notation (3.00E +04)
inappropriately. I click on the column and format the number type for the
column to be "General", which then converts the Scientific Notation to a
number (e.g. 30000). We correct the number to 3E4 but it then reverts back
to Scientific Notation. When we check the format of the cell it has switched
from General back to Scientific.

How can we avoid this and get the spread sheet to accept the format 3E4 or
4E1 or whatever it should be?

 

[Bernard Liengme]

The simplest way, if you have control of the third-party software, would be
to export the identifies with a preceding apostrophe, i.e. '4E6, '3B4, etc.
This would not show in the Excel cell (or in a printout) but would force the
item to be text.
best wishes

 

[Ron Rosenfeld]

We import results into Excel 2003 from third party software. One column is
populated with identifiers such as 3B2, 3C2, 3E4 etc. Any identifiers that
have an "E" in them convert to Scientific Notation (3.00E +04)
inappropriately. I click on the column and format the number type for the
column to be "General", which then converts the Scientific Notation to a
number (e.g. 30000). We correct the number to 3E4 but it then reverts back
to Scientific Notation. When we check the format of the cell it has switched
from General back to Scientific.

How can we avoid this and get the spread sheet to accept the format 3E4 or
4E1 or whatever it should be?

The solution is to format the column as TEXT **before** you import.

The best way to do that will depend on exactly how you import your data.


--ron

 

[Guest]

Thanks for the suggestions and I will explore these options with the third
party software - however I believe we have limited control over this. In the
meanwhile, how can I correct the incorrect values back to the appropriate
values? Excel seems to revert back to Scientific each time we type an
identifier containing an E. I would think this would be possible but will
use the apostrophe as a work around unless I hear otherwise.

 

[Ron Rosenfeld]

Since you did not mention exactly how you import your data, it is not possible
to advise you as to how to best avoid the problem. If you care to share that,
there may be some solution available without modifying the third party
software.

That is why I wrote that "The best way to do that will depend on exactly how
you import your data."

If the identifiers with the "E" only consist of a single number in the range of
1-9 prior to the "E" (in other words, 1En, 2En, 3En, ...; but not 10En) and
where 'n' is any number, then you could use this formula to convert the
entries:

=SUBSTITUTE(TEXT(E1,"0E+0"),"+","")

However, as you can see, the allowable data is pretty limited.

Thanks for the suggestions and I will explore these options with the third
party software - however I believe we have limited control over this. In the
meanwhile, how can I correct the incorrect values back to the appropriate
values? Excel seems to revert back to Scientific each time we type an
identifier containing an E. I would think this would be possible but will
use the apostrophe as a work around unless I hear otherwise.

--ron

 

[Guest]

I'm having the same problem. We have an internal field that is 9 characters
in length, and formatted: 3char '08' 3char.

Example: type 61208E12 into a cell and it is reformatted as 6.12E+16. Can
the conversion to scientific be turned off?
Thanks,
Karen

 

[Dave Peterson]

Preformat the cell as text. Then do your typing.

Or prefix the entry with an apostrophe: '61208E12