scientific notation

so I'm importing excel data into access.. the problems fields are
mostly text.. but some large integers...
and the large integers (stored in a text column in excel) keep on
getting changed to scientific notation.


is there somewhere that i can tell windows or excel-- or something--
to NEVER EVER EVER use scientific notation?

i mean seriously here

is there a way to make excel WORK?

because it's a crap program and i dont understand why you babies use
this program.

i mean-- grow up and learn a real program.. all you're doing is
cramping my style.

On 31 May 2005 09:57:04 -0700, "[email protected]" <[email protected]>
wrote:

>i mean seriously here
>
>is there a way to make excel WORK?
>
>because it's a crap program and i dont understand why you babies use
>this program.
>
>i mean-- grow up and learn a real program.. all you're doing is
>cramping my style.

It sounds like it is you that should either learn the program that you are
using; or use a program that meets your requirements.


Since you think Excel is "a crap program", why are you even here?

Troll?


--ron

i believe that Excel is mis-managed by the worlds most powerful
software company.

Excel doesnt work.

I mean-- i have a column with mixed large numbers; and excel keeps on
rendering it as scientific notation.. i mean-- wtf is up with Excel; it
is like the most buggy program ever written

You obviously don't have very much experience with other software. XL
has bugs, but there are far more worthy candidates for your hyperbole.

Take a look at "About custom number formats" in XL Help.


In article <[email protected]>,
"[email protected]" <[email protected]> wrote:

> Excel doesnt work.
>
> I mean-- i have a column with mixed large numbers; and excel keeps on
> rendering it as scientific notation.. i mean-- wtf is up with Excel; it
> is like the most buggy program ever written

Don't waste your time. The guy is either a troll or the most immature
person to visit a newsgroup. He'd probably walk into an AA meeting,
offering to buy a round for the group and when no one took him up
on his offer, start preaching that AA is a cult.

"JE McGimpsey"wrote
> You obviously don't have very much experience with other software. XL
> has bugs, but there are far more worthy candidates for your hyperbole.
>
> Take a look at "About custom number formats" in XL Help.
>
>
> In article <[email protected]>,
> "[email protected]" <[email protected]> wrote:
>
> > Excel doesnt work.
> >
> > I mean-- i have a column with mixed large numbers; and excel keeps on
> > rendering it as scientific notation.. i mean-- wtf is up with Excel; it
> > is like the most buggy program ever written

the formatting doesn't work as advertised.

how do i get rid of scientific notation in cells from excel?

i never want to see any scientific notation in another cell-- ever
again.

how do i import it into a database without having it cough?

it should all be easier-- it should all be more reliable.

and i just wish that MS would fix these bugs.. i mean.. use the
product-- get a newbie and watch them and make it more useable.

it doesn't work.. i've worked with hundreds of newbies before.. and im
just tired of apologizing for the bugginess of the software..

i just wish that things would work reliably.

<[email protected]> wrote...
>the formatting doesn't work as advertised.

Yes it does, but you don't seem to understand the specs.

>how do i get rid of scientific notation in cells from excel?
....

Set the number format to something other than Scientific *OR* General. If
you use the General number format, you'll get numbers in scientific notation
when columns aren't wide enough to display in normal format. This is no
different from many other numeric applications that use a 'general' format
by default to display floating point reals.

>how do i import it into a database without having it cough?

Again, set the number format correctly, and you shouldn't have a problem. If
you do in some database, then the problem is in the *DATABASE*, not Excel,
so further bitching & moaning in this newsgroup would be OT.

i dont believe that works correctly.

sorry.

i want microsoft to fix it.

<[email protected]> wrote
> i dont believe that works correctly.
>
> sorry.
>
> i want microsoft to fix it.

And I want to win the Lotto

yeah. well.. i'm not the one that is selling crappy software

<[email protected]> wrote...
>i dont believe that works correctly.

You can believe the world is flat, the universe is centered on the Earth,
you're not obtuse, and other demonstrably false propositions and it won't
make them true.

FORMAT CELLS CONTAINING NUMBERS TO ENSURE THEY DISPLAY THE WAY YOU WANT.
*YOUR* failure to do so is *YOUR* problem alone, and there's squat all
Microsoft needs to fix (other than perhaps offering to pay for you to have a
lobotomy and/or one-way transit to a deserted island in the Indian Ocean).

>i want microsoft to fix it.

What's to fix? This is *DOCUMENTED* functionality, *AND* it's familiar to
*ANYONE* who's used *ANY* spreadsheet under MS/PC-DOS or Windows. Also
familiar to anyone who's programmed in any scripting language including VBA
(e.g., enter the following statements in the VBE Immediate window

? (1234# * 5678#) ^ 9#

? (12# * 34#) ^ 5#

). Anyone who knows C's printf format strings knows the difference between
%e (scientific), %f ('normal') and %g ('general') floating point number
formats. It's also part of FORTRAN 95,

http://www.ncsa.uiuc.edu/UserInfo/Re...HTM#HDRH000062

That you don't understand this concept is obvious, but just because you're
unwilling or unable to grasp this doesn't mean there's anything for
Microsoft to fix.

And to repeat, if numeric data is being pulled into some DBMS in scientific
format, doesn't the DBMS provide facilities to format those numbers some
other way? Or are you saying the *DBMS* is importing numeric data in
scientific format as text? Either way, it ain't Excel causing the problem.

<[email protected]> wrote...
>yeah. well.. i'm not the one that is selling crappy software

No, you're the one who refuses to accept how the software works and refuses
to understant that it works the same way nearly all numeric software does
when representing floating point numbers of arbitrary magnitude as text on
output when you the user haven't bothered to specify your desired number
format.

For some, ignorance is bliss. For you it's just a way of life.

i dont want to win the lotto

i just want to use products from a company that is HELD ACCOUNTABLE FOR
THEIR BUGS

microsoft is fat and lazy

> [email protected] wrote...


"Harlan Grove" wrote
> you'd much rather *****,
^^^^^^

You misspelled troll

Jordon

[email protected] wrote...
>the DBMS doesnt have a problem
....

Should have concentrated on this statement. Numbers imported from an
..XLS file that appear in scientific format in the .XLS file would come
through as either numbers or text in the database.

If they come through as numbers but merely appear in scientific format,
can't your database display them in a different number format? Are you
just complaining because you don't know how to change number formats?

If they come through as text, but (to repeat) they were numbers in
Excel, then how can even your tiny, poorly functioning brain conclude
that Excel (in which these were numbers) is in any way responsible for
how your database imports these cells? It'd be the database that's
importing the .Text rather than .Value property of these cells, then
failing to be able to recognize scientific notation as a valid number
format.

Now, if I've been misinterpretting your complaint, and in fact you mean
*ACCESS* is crappy software, then you have a point. IMO, it really
sucks that Access can't handle COUNT(DISTINCT ..) and can't handle * as
Field and COUNT as function in the Query Builder.

no; they are coming in as scientific notation

i have a column called part number or something lke that

when it sees a part number 9123456789012 it converts that to scientific
notation. and excel hoses up the data.

the column isn't all numeric-- i can't make it all numeric because it
has some part # 1445H; etc

I just can't figure out how to handle this on the excel side.

I've written UDFs that can solve this on the db side-- that part is
easy.

i just wish that i could turn off all scientific notation on a workbook

<[email protected]> wrote
> when it sees a part number 9123456789012 it converts that to scientific
> notation. and excel hoses up the data.

Please, we're all dying to know. Just what does 9123456789012 look
like when it comes out the other end?

Jordon

[email protected] wrote...
>no; they are coming in as scientific notation

To repeat: that's your fault.

>i have a column called part number or something lke that
>
>when it sees a part number 9123456789012 it converts that to scientific
>notation. and excel hoses up the data.

In Excel, the *VALUE* of the cell remains 9123456789012, even though it
would display as 9.234E12. If you need it to display as 9123456789012,
it's up to *YOU* to format it as Number with zero decimal places or (a
better idea) as Text.

>the column isn't all numeric-- i can't make it all numeric because it
>has some part # 1445H; etc

Again, *YOUR* screw-up. Part numbers aren't cardinal numbers, they're
identifiers that may use nothing more than decimal numerals. *YOU*
should format such cells with number format Text to ensure that other
software, such as your pet database, interpret all values in the field
correctly as text represented as you want it represented.

It's *YOUR* fault some of these part numbers appear in scientific
notation, and evidently *YOU* are too stupid and pigheaded to realize
this and do what *YOU* need to do to fix this.

>I just can't figure out how to handle this on the excel side.
....

Try reading the article

http://groups-beta.google.com/group/...e=source&hl=en

(or http://makeashorterlink.com/?Q2334334B ) and applying the approach
therein. It really isn't hard. This falls under the category 'When all
else fails, and you've bitched, whined & moaned all you want, try
reading the directions'.

>i just wish that i could turn off all scientific notation on a workbook

It's easy to do, but you can't figure it out because your spreadsheet
knowledge is utterly lacking. You're dirt ignorant and incapable of
learning anything on the spreadsheet side. Face it, you've found out
the boundary of your own stupidity.

9.05455343543e12 or something retarded.

i can trap it in the db side; im just worried about losing digits
and/or decimals

-aaron

9.05455343543e12 or something retarded.

i can trap it in the db side; im just worried about losing digits
and/or decimals

-aaron

RE:
------------
In Excel, the *VALUE* of the cell remains 9123456789012, even though it

would display as 9.234E12. If you need it to display as 9123456789012,
it's up to *YOU* to format it as Number with zero decimal places or (a
better idea) as Text.
--------------

The reason i hate excel so much

is because i dont EVER want a difference between what i 'see' and what
is actually stored.

I want to be able to turn off that functionality
i mean-- i can in Access; i can in SQL

wtf is excel unable to do simple things like this?

-aaron

[email protected] wrote...
....
>The reason i hate excel so much
>
>is because i dont EVER want a difference between what i 'see' and what
>is actually stored.

Then you must necessarily hate all software except hex browsers because
nothing other than hex browsers (or binary browsers if there are such
beasts) displays what truly stored.

*YOUR* problem in this case is that you're too stupid, lazy and
ignorant to learn basic data migration techniques that have been
well-known for DECADES. If you have a column that contains what should
always be treated as text, FORMAT THE BLOODY COLUMN AS TEXT. It's not
that difficult, but it does require understanding the need to do so,
knowing how to do it, and doing it, all of which you seem incapable.

If you format as text the problem column you've described, you wouldn't
be having the problem you've been bitching, whining & moaning about. It
seems, based on the evidence in this string, that you'd much rather
keep complaining than learning how to fix your problem.

>I want to be able to turn off that functionality
>i mean-- i can in Access; i can in SQL

This works differently in Access or any database because database
fields (columns) are always of a single specified data type. For some
(e.g., you) this necessary rigidity of design is a benefit. Others can
view it differently.

Besides, you *CAN* turn this of in Excel. All it takes is changing the
number format to Text. Not hard, but apparently harder than you're
capable of dealing with.

>wtf is excel unable to do simple things like this?

No, it's more like wtf Aaron can't learn how to do something this
simple.

[email protected] wrote...
>9.05455343543e12 or something retarded.
>
>i can trap it in the db side; im just worried about losing digits
>and/or decimals

Then you're being an even more obtuse moron that I had already figured.
Neither Excel nor Access can handle *NUMBERS* with more than 15 decimal
digits. If data entry takes place in Excel, then you MUST format the
cells as Text if you want to preserve more than 15 decimal digits.

Try to learn something for a change!

Whow

GO YOU, Harlan......

See what Aaron has got to say to THAT.
( :> ))
--
Greetings from New Zealand
Bill K

"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> <[email protected]> wrote...
>>i dont believe that works correctly.
>
> You can believe the world is flat, the universe is centered on the Earth,
> you're not obtuse, and other demonstrably false propositions and it won't
> make them true.
>
> FORMAT CELLS CONTAINING NUMBERS TO ENSURE THEY DISPLAY THE WAY YOU WANT.
> *YOUR* failure to do so is *YOUR* problem alone, and there's squat all
> Microsoft needs to fix (other than perhaps offering to pay for you to have
> a
> lobotomy and/or one-way transit to a deserted island in the Indian Ocean).
>
>>i want microsoft to fix it.
>
> What's to fix? This is *DOCUMENTED* functionality, *AND* it's familiar to
> *ANYONE* who's used *ANY* spreadsheet under MS/PC-DOS or Windows. Also
> familiar to anyone who's programmed in any scripting language including
> VBA
> (e.g., enter the following statements in the VBE Immediate window
>
> ? (1234# * 5678#) ^ 9#
>
> ? (12# * 34#) ^ 5#
>
> ). Anyone who knows C's printf format strings knows the difference between
> %e (scientific), %f ('normal') and %g ('general') floating point number
> formats. It's also part of FORTRAN 95,
>
> http://www.ncsa.uiuc.edu/UserInfo/Re...HTM#HDRH000062
>
> That you don't understand this concept is obvious, but just because you're
> unwilling or unable to grasp this doesn't mean there's anything for
> Microsoft to fix.
>
> And to repeat, if numeric data is being pulled into some DBMS in
> scientific
> format, doesn't the DBMS provide facilities to format those numbers some
> other way? Or are you saying the *DBMS* is importing numeric data in
> scientific format as text? Either way, it ain't Excel causing the problem.
>
>

Why chance reigniting a thread like this?

For the record, I'm having problems in this area also. I found a workaround, but it required modifying the data before I import it, which is annoying.

I imported a text file containing a column of IDs that consist of numbers with an alpha suffix. I needed to strip off the alpha and keep the number as-is, with the leading zeroes.

I imported the column as "Text" format and everything was fine, but then I did a "replace all" to remove the alpha portion and Excel converted everything to scientific notation - this in spite of the fact that the column format is text. Changing the format to "General" does not fix it. Changing it to "Number" takes it out of scientific notation but loses my leading zeroes.

I don't think Excel should change the format of a cell once I've specified it - if I want to change it I will. Is it trying to anticipate my needs? Is there a way to tell it not to?