16 digit # formats to Exponential & rounds off???

[Gary Schirmer]

I am using Excel 2000. Excel likes to take a 16 digit number and change it
to Exponential format, and change the last digit to "0" (zero). This happens
when I output the results of an SQL Query in Goldmine to Excel:

Example, 8773103250909926 in Goldmine becomes 8.7731E+15 when output to
excel and when I click on the cell in Excel, the number in the window is
8773103250909920, the final 6 becoming a "0" (zero). This makes the number
totally useless (as it is an account number.)

Is there any way to turn off this autoformatting feature of Excel?

 

[Peo Sjoblom]

Look in help for specifications, excel has 15 digits precision, everything
beyond will be truncated, only way is to use text format if you want store
account numbers credit cards etc

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Gary Schirmer]

Thanks for your reply. I understand what you write, but I cannot define the
format of the field in Excel as text prior to exporitng to it from Goldmine.
That 15 digit precision applies to decimal points if I'm correct, which comes
into play only because excel changes the number to exponential notation. Is
there any way to prevent this conversion to exponential format? Or is this
hard coded into Excel?

 

[Tom Ogilvy]

That 15 digit precision applies to decimal points if I'm correct,

No, you aren't correct. The only solution is to convert your numbers to
string. This should be possible in your SQL query.

 

[David McRitchie]

Hi Gary,
Change the file extension from .csv to .txt then
you open the file in Excel with the import wizard,
and you can define the field(s) you want as text when bringing file
into Excel.

If you don't define it as text, I think Excel will assume a number whether
it is enclosed in quotes or not.

 

[Gary Schirmer]

Thanks to both of you who responded. I have come up with a resolution that
comes from reading both ideas. but not exactly what you suggested. I will
be adding a letter to the number to force excel to treat is as text. A
simple solution that will preserve the integrity of the number, and workable
in the end product.

Thanks again.

Hi Gary,
Change the file extension from .csv to .txt then
you open the file in Excel with the import wizard,
and you can define the field(s) you want as text when bringing file
into Excel.

If you don't define it as text, I think Excel will assume a number whether
it is enclosed in quotes or not.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Thanks for your reply. I understand what you write, but I cannot define the
format of the field in Excel as text prior to exporitng to it from Goldmine.
That 15 digit precision applies to decimal points if I'm correct, which comes
into play only because excel changes the number to exponential notation. Is
there any way to prevent this conversion to exponential format? Or is this
hard coded into Excel?