Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
berryware421243
 
Posts: n/a
Default Long numbers show up as Scientific Notation

Hello,

We have a web based asset tracking system, and some assets have long serial
numbers; for instance,
3100600300030147987
or
3101600300100020220

When we import, cut and paste, or open an HTML format page in Excel with
these serial numbers, they all show up as: 3.1016E+18 and the last 6 digits
get rounded up.

How do we show the correct value for these long numbers in excel?

Thanks


  #2   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Mon, 7 Feb 2005 08:59:07 -0800, berryware421243
wrote:

Hello,

We have a web based asset tracking system, and some assets have long serial
numbers; for instance,
3100600300030147987
or
3101600300100020220

When we import, cut and paste, or open an HTML format page in Excel with
these serial numbers, they all show up as: 3.1016E+18 and the last 6 digits
get rounded up.

How do we show the correct value for these long numbers in excel?

Thanks


These must be imported as TEXT.


--ron
  #3   Report Post  
CarlosAntenna
 
Posts: n/a
Default

Excel won't work with numbers longer than 15 digits. You don't say exactly
how you are importing the data, but somewhere in the process you need to
identify the serial numbers as text. Maybe you could format the destination
cell as text or precede the number with an apostrophe ( ' ). The apostrophe
won't display or print, but will make the number a string.

Carlos

"berryware421243" wrote in
message ...
Hello,

We have a web based asset tracking system, and some assets have long

serial
numbers; for instance,
3100600300030147987
or
3101600300100020220

When we import, cut and paste, or open an HTML format page in Excel with
these serial numbers, they all show up as: 3.1016E+18 and the last 6

digits
get rounded up.

How do we show the correct value for these long numbers in excel?

Thanks




  #4   Report Post  
berryware421243
 
Posts: n/a
Default

Hello,
We are exporting the result set of a query from the application as an HTML
table. This HTM file is opened in Excel and the long serial numbers get
truncated.

The same happens when we cut and paste.

We cannot prefix the number with another character as the HTML table export
is part of the application.

Thanks

"Ron Rosenfeld" wrote:

On Mon, 7 Feb 2005 08:59:07 -0800, berryware421243
wrote:

Hello,

We have a web based asset tracking system, and some assets have long serial
numbers; for instance,
3100600300030147987
or
3101600300100020220

When we import, cut and paste, or open an HTML format page in Excel with
these serial numbers, they all show up as: 3.1016E+18 and the last 6 digits
get rounded up.

How do we show the correct value for these long numbers in excel?

Thanks


These must be imported as TEXT.


--ron

  #5   Report Post  
berryware
 
Posts: n/a
Default

Hello,
We are exporting the result set of a query from the application as an HTML
table. This HTM file is opened in Excel and the long serial numbers get
truncated.

The same happens when we cut and paste.

We cannot prefix the number with another character as the HTML table export
is part of the application.

Thanks

"CarlosAntenna" wrote:

Excel won't work with numbers longer than 15 digits. You don't say exactly
how you are importing the data, but somewhere in the process you need to
identify the serial numbers as text. Maybe you could format the destination
cell as text or precede the number with an apostrophe ( ' ). The apostrophe
won't display or print, but will make the number a string.

Carlos

"berryware421243" wrote in
message ...
Hello,

We have a web based asset tracking system, and some assets have long

serial
numbers; for instance,
3100600300030147987
or
3101600300100020220

When we import, cut and paste, or open an HTML format page in Excel with
these serial numbers, they all show up as: 3.1016E+18 and the last 6

digits
get rounded up.

How do we show the correct value for these long numbers in excel?

Thanks







  #6   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Mon, 7 Feb 2005 11:19:02 -0800, berryware421243
wrote:

Hello,
We are exporting the result set of a query from the application as an HTML
table. This HTM file is opened in Excel and the long serial numbers get
truncated.

The same happens when we cut and paste.

We cannot prefix the number with another character as the HTML table export
is part of the application.

Thanks


If you cannot change how the application exports the data, you will probably
have to write a routine that preprocesses the HTML table before importing it
into Excel, in order that Excel may treat those long numbers as text. This is
not an area in which I am knowledgeable, however.

However, try the following procedu

=========================
Copy the table.

Open Excel and format the cells as TEXT.

Paste/Special as TEXT

Clean up the mess (extra rows and so forth.
===================

--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Why cant you show negative numbers in parentheses? Bruce Excel Discussion (Misc queries) 5 December 6th 05 05:26 PM
displaying numbers in engineering notation Joshua Wong Excel Discussion (Misc queries) 5 January 31st 05 04:50 PM
Removing Scientific Notation Trisha Lynn New Users to Excel 8 January 20th 05 07:38 PM
convert scientific notation to a number Peter Excel Discussion (Misc queries) 1 January 4th 05 08:08 PM
Have negative numbers show as zero Domenic Excel Worksheet Functions 0 November 9th 04 05:18 PM


All times are GMT +1. The time now is 07:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"