The AccuTerm forum has moved. Go to community.rocketsoftware.com to register for the new Rocket forum. |
Excel numeric data in exp notation |
Post Reply |
Author | |
anonymous
Groupie Joined: January 15 2004 Location: United States Status: Offline Points: 46 |
Post Options
Thanks(0)
Posted: July 07 2004 at 11:35am |
When transferring info from PICK to Excel using FTD, a number column comes up with strange looking characters, i.e. 2.11112E+11 when the number on file is a 16-digit straight numeric field. When I change the format in Excel to "numbers," it drops the 1st zero digit, i.e. 0211111111111 becomes 211111111111.
|
|
PSchellenbach
Admin Group Moderator Joined: December 15 2003 Location: United States Status: Offline Points: 2150 |
Post Options
Thanks(0)
|
This is one of those annoying quirks of Excel - it always tries to convert numeric columns into Excel "general number" format. In this case it is converting to scientific notation - which is not what you want! I tested a couple of solutions with AccuTerm 2K2 release 5.2a. Older releases may not work the same so if your not running at least 5.2a, you might want to upgrade.
The Excel converter in release 5.2a examines (at least part of) the column data, and if it finds leading zeros, it forces Excel to import the column as text. If there are no leading zeros, Excel converts the data into its general number format, and if there are lots of digits, this will be in scientific notation. If you pre-format the column and save the workbook, you can export to the existing workbook file using the existing (saved) formatting. Select the column and Format Cells as "custom format". Set the format to #. Adjust the Alignment if desired. Delete all rows of the worksheet except the heading row(s) and save the workbook file. When using FTD, export to the existing workbook file, and specify a starting cell of A2 (of the already created and formatted worksheet). This will presere the formatting for the column and the new data will look normal. For example, use "c:\tmp\test.xls!!A2" for the target file name to export to existing workbook 'test.xls', storing the exported data beginning at cell A2. |
|
Post Reply | |
Tweet
|
Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |