Cell format problem

[Barb]

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Barb" <[email protected]> wrote in message
news:[email protected]...
> 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?

[Ron Rosenfeld]

On Thu, 5 Jan 2006 10:33:04 -0800, "Barb" <[email protected]>
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 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

[Barb]

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" <[email protected]>
> 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 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
>

[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.



On Thu, 5 Jan 2006 11:38:14 -0800, "Barb" <[email protected]>
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" <[email protected]>
>> 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 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
>>

--ron

[Karen M]

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" <[email protected]>
> 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" <[email protected]>
> >> 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 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
> >>
>
> --ron
>

[Dave Peterson]

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" <[email protected]>
> > 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" <[email protected]>
> > >> 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 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
> > >>
> >
> > --ron
> >

--

Dave Peterson