Excel scientific notation

[arrow483]

I have some 3rd party extracts that I need to upload from Excel. It all works fine except when I get this scientific notation like 1.6931E+00

Its a char field on the iseries, and the column has mixed char and numeric in the Excel. (Its a postal code). I can resolve this by adding a column to the Excel sheet and using a formula like =TEXT(T142,"0") and then uploading that column. But I have way too many spreadsheets, and columns (phone numbers, etc) for this crap.

Anyone found either (1.) a way to fix Excel or (2.) a way to convert this uploaded field back to a "real" value. My guess is the second option will produce a estimate ? Which of course is no value with a 9 digit zip code.

[tomholden]

Re: Excel scientific notation

on the upload using the Excel add-in for transfer perhaps create a file based on the spreadsheet and see what you get. otherwise you might try defining the field in question as type float in the target file.

[arrow483]

Re: Excel scientific notation

Thanks for the suggestion. The rows contains a mixture of 5 digit zips, 9 digit zips, alpha chars (for Canada and elsewhere), and some 9-digit zip with an imbedded "-". CA sees them as "unicode", and the database as char, its all good exept these stupid 9 digit values that look numeric.