Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why cant you show negative numbers in parentheses? | Excel Discussion (Misc queries) | |||
displaying numbers in engineering notation | Excel Discussion (Misc queries) | |||
Removing Scientific Notation | New Users to Excel | |||
convert scientific notation to a number | Excel Discussion (Misc queries) | |||
Have negative numbers show as zero | Excel Worksheet Functions |