Re: Cell format problem

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Preformat the cell as text. Then do your typing.

Or prefix the entry with an apostrophe: '61208E12

Karen M wrote:

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

"Ron Rosenfeld" wrote:

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.



On Thu, 5 Jan 2006 11:38:14 -0800, "Barb" <Barb@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

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" wrote:

On Thu, 5 Jan 2006 10:33:04 -0800, "Barb" <Barb@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

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 *** 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


--ron


--

Dave Peterson
.

Quantcast