software

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 quot;Equot; 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 quot;Generalquot;, 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 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

quot;Barbquot; gt; wrote in message
...
gt; We import results into Excel 2003 from third party software. One column
gt; is
gt; populated with identifiers such as 3B2, 3C2, 3E4 etc. Any identifiers
gt; that
gt; have an quot;Equot; in them convert to Scientific Notation (3.00E 04)
gt; inappropriately. I click on the column and format the number type for the
gt; column to be quot;Generalquot;, which then converts the Scientific Notation to a
gt; number (e.g. 30000). We correct the number to 3E4 but it then reverts
gt; back
gt; to Scientific Notation. When we check the format of the cell it has
gt; switched
gt; from General back to Scientific.
gt;
gt; How can we avoid this and get the spread sheet to accept the format 3E4 or
gt; 4E1 or whatever it should be?
On Thu, 5 Jan 2006 10:33:04 -0800, quot;Barbquot; gt;
wrote:

gt;We import results into Excel 2003 from third party software. One column is
gt;populated with identifiers such as 3B2, 3C2, 3E4 etc. Any identifiers that
gt;have an quot;Equot; in them convert to Scientific Notation (3.00E 04)
gt;inappropriately. I click on the column and format the number type for the
gt;column to be quot;Generalquot;, which then converts the Scientific Notation to a
gt;number (e.g. 30000). We correct the number to 3E4 but it then reverts back
gt;to Scientific Notation. When we check the format of the cell it has switched
gt;from General back to Scientific.
gt;
gt;How can we avoid this and get the spread sheet to accept the format 3E4 or
gt;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

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.

quot;Ron Rosenfeldquot; wrote:

gt; On Thu, 5 Jan 2006 10:33:04 -0800, quot;Barbquot; gt;
gt; wrote:
gt;
gt; gt;We import results into Excel 2003 from third party software. One column is
gt; gt;populated with identifiers such as 3B2, 3C2, 3E4 etc. Any identifiers that
gt; gt;have an quot;Equot; in them convert to Scientific Notation (3.00E 04)
gt; gt;inappropriately. I click on the column and format the number type for the
gt; gt;column to be quot;Generalquot;, which then converts the Scientific Notation to a
gt; gt;number (e.g. 30000). We correct the number to 3E4 but it then reverts back
gt; gt;to Scientific Notation. When we check the format of the cell it has switched
gt; gt;from General back to Scientific.
gt; gt;
gt; gt;How can we avoid this and get the spread sheet to accept the format 3E4 or
gt; gt;4E1 or whatever it should be?
gt;
gt; The solution is to format the column as TEXT **before** you import.
gt;
gt; The best way to do that will depend on exactly how you import your data.
gt;
gt;
gt; --ron
gt;

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 quot;The best way to do that will depend on exactly how
you import your data.quot;

If the identifiers with the quot;Equot; only consist of a single number in the range of
1-9 prior to the quot;Equot; (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,quot;0E 0quot;),quot; quot;,quot;quot;)

However, as you can see, the allowable data is pretty limited.
On Thu, 5 Jan 2006 11:38:14 -0800, quot;Barbquot; gt;
wrote:

gt;Thanks for the suggestions and I will explore these options with the third
gt;party software - however I believe we have limited control over this. In the
gt;meanwhile, how can I correct the incorrect values back to the appropriate
gt;values? Excel seems to revert back to Scientific each time we type an
gt;identifier containing an E. I would think this would be possible but will
gt;use the apostrophe as a work around unless I hear otherwise.
gt;
gt;quot;Ron Rosenfeldquot; wrote:
gt;
gt;gt; On Thu, 5 Jan 2006 10:33:04 -0800, quot;Barbquot; gt;
gt;gt; wrote:
gt;gt;
gt;gt; gt;We import results into Excel 2003 from third party software. One column is
gt;gt; gt;populated with identifiers such as 3B2, 3C2, 3E4 etc. Any identifiers that
gt;gt; gt;have an quot;Equot; in them convert to Scientific Notation (3.00E 04)
gt;gt; gt;inappropriately. I click on the column and format the number type for the
gt;gt; gt;column to be quot;Generalquot;, which then converts the Scientific Notation to a
gt;gt; gt;number (e.g. 30000). We correct the number to 3E4 but it then reverts back
gt;gt; gt;to Scientific Notation. When we check the format of the cell it has switched
gt;gt; gt;from General back to Scientific.
gt;gt; gt;
gt;gt; gt;How can we avoid this and get the spread sheet to accept the format 3E4 or
gt;gt; gt;4E1 or whatever it should be?
gt;gt;
gt;gt; The solution is to format the column as TEXT **before** you import.
gt;gt;
gt;gt; The best way to do that will depend on exactly how you import your data.
gt;gt;
gt;gt;
gt;gt; --ron
gt;gt;

--ron

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

quot;Ron Rosenfeldquot; wrote:

gt; Since you did not mention exactly how you import your data, it is not possible
gt; to advise you as to how to best avoid the problem. If you care to share that,
gt; there may be some solution available without modifying the third party
gt; software.
gt;
gt; That is why I wrote that quot;The best way to do that will depend on exactly how
gt; you import your data.quot;
gt;
gt; If the identifiers with the quot;Equot; only consist of a single number in the range of
gt; 1-9 prior to the quot;Equot; (in other words, 1En, 2En, 3En, ...; but not 10En) and
gt; where 'n' is any number, then you could use this formula to convert the
gt; entries:
gt;
gt; =SUBSTITUTE(TEXT(E1,quot;0E 0quot;),quot; quot;,quot;quot;)
gt;
gt; However, as you can see, the allowable data is pretty limited.
gt;
gt;
gt;
gt; On Thu, 5 Jan 2006 11:38:14 -0800, quot;Barbquot; gt;
gt; wrote:
gt;
gt; gt;Thanks for the suggestions and I will explore these options with the third
gt; gt;party software - however I believe we have limited control over this. In the
gt; gt;meanwhile, how can I correct the incorrect values back to the appropriate
gt; gt;values? Excel seems to revert back to Scientific each time we type an
gt; gt;identifier containing an E. I would think this would be possible but will
gt; gt;use the apostrophe as a work around unless I hear otherwise.
gt; gt;
gt; gt;quot;Ron Rosenfeldquot; wrote:
gt; gt;
gt; gt;gt; On Thu, 5 Jan 2006 10:33:04 -0800, quot;Barbquot; gt;
gt; gt;gt; wrote:
gt; gt;gt;
gt; gt;gt; gt;We import results into Excel 2003 from third party software. One column is
gt; gt;gt; gt;populated with identifiers such as 3B2, 3C2, 3E4 etc. Any identifiers that
gt; gt;gt; gt;have an quot;Equot; in them convert to Scientific Notation (3.00E 04)
gt; gt;gt; gt;inappropriately. I click on the column and format the number type for the
gt; gt;gt; gt;column to be quot;Generalquot;, which then converts the Scientific Notation to a
gt; gt;gt; gt;number (e.g. 30000). We correct the number to 3E4 but it then reverts back
gt; gt;gt; gt;to Scientific Notation. When we check the format of the cell it has switched
gt; gt;gt; gt;from General back to Scientific.
gt; gt;gt; gt;
gt; gt;gt; gt;How can we avoid this and get the spread sheet to accept the format 3E4 or
gt; gt;gt; gt;4E1 or whatever it should be?
gt; gt;gt;
gt; gt;gt; The solution is to format the column as TEXT **before** you import.
gt; gt;gt;
gt; gt;gt; The best way to do that will depend on exactly how you import your data.
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; --ron
gt; gt;gt;
gt;
gt; --ron
gt;

Preformat the cell as text. Then do your typing.

Or prefix the entry with an apostrophe: '61208E12

Karen M wrote:
gt;
gt; I'm having the same problem. We have an internal field that is 9 characters
gt; in length, and formatted: 3char '08' 3char.
gt;
gt; Example: type 61208E12 into a cell and it is reformatted as 6.12E 16. Can
gt; the conversion to scientific be turned off?
gt; Thanks,
gt; Karen
gt;
gt; quot;Ron Rosenfeldquot; wrote:
gt;
gt; gt; Since you did not mention exactly how you import your data, it is not possible
gt; gt; to advise you as to how to best avoid the problem. If you care to share that,
gt; gt; there may be some solution available without modifying the third party
gt; gt; software.
gt; gt;
gt; gt; That is why I wrote that quot;The best way to do that will depend on exactly how
gt; gt; you import your data.quot;
gt; gt;
gt; gt; If the identifiers with the quot;Equot; only consist of a single number in the range of
gt; gt; 1-9 prior to the quot;Equot; (in other words, 1En, 2En, 3En, ...; but not 10En) and
gt; gt; where 'n' is any number, then you could use this formula to convert the
gt; gt; entries:
gt; gt;
gt; gt; =SUBSTITUTE(TEXT(E1,quot;0E 0quot;),quot; quot;,quot;quot;)
gt; gt;
gt; gt; However, as you can see, the allowable data is pretty limited.
gt; gt;
gt; gt;
gt; gt;
gt; gt; On Thu, 5 Jan 2006 11:38:14 -0800, quot;Barbquot; gt;
gt; gt; wrote:
gt; gt;
gt; gt; gt;Thanks for the suggestions and I will explore these options with the third
gt; gt; gt;party software - however I believe we have limited control over this. In the
gt; gt; gt;meanwhile, how can I correct the incorrect values back to the appropriate
gt; gt; gt;values? Excel seems to revert back to Scientific each time we type an
gt; gt; gt;identifier containing an E. I would think this would be possible but will
gt; gt; gt;use the apostrophe as a work around unless I hear otherwise.
gt; gt; gt;
gt; gt; gt;quot;Ron Rosenfeldquot; wrote:
gt; gt; gt;
gt; gt; gt;gt; On Thu, 5 Jan 2006 10:33:04 -0800, quot;Barbquot; gt;
gt; gt; gt;gt; wrote:
gt; gt; gt;gt;
gt; gt; gt;gt; gt;We import results into Excel 2003 from third party software. One column is
gt; gt; gt;gt; gt;populated with identifiers such as 3B2, 3C2, 3E4 etc. Any identifiers that
gt; gt; gt;gt; gt;have an quot;Equot; in them convert to Scientific Notation (3.00E 04)
gt; gt; gt;gt; gt;inappropriately. I click on the column and format the number type for the
gt; gt; gt;gt; gt;column to be quot;Generalquot;, which then converts the Scientific Notation to a
gt; gt; gt;gt; gt;number (e.g. 30000). We correct the number to 3E4 but it then reverts back
gt; gt; gt;gt; gt;to Scientific Notation. When we check the format of the cell it has switched
gt; gt; gt;gt; gt;from General back to Scientific.
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt;How can we avoid this and get the spread sheet to accept the format 3E4 or
gt; gt; gt;gt; gt;4E1 or whatever it should be?
gt; gt; gt;gt;
gt; gt; gt;gt; The solution is to format the column as TEXT **before** you import.
gt; gt; gt;gt;
gt; gt; gt;gt; The best way to do that will depend on exactly how you import your data.
gt; gt; gt;gt;
gt; gt; gt;gt;
gt; gt; gt;gt; --ron
gt; gt; gt;gt;
gt; gt;
gt; gt; --ron
gt; gt;

--

Dave Peterson