scientific notation

[Harlan Grove]

(e-mail address removed) 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/microsoft.public.excel/msg/917a463990eed6e7?dmode=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.

 

[aaron.kempf]

9.05455343543e12 or something retarded.

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

-aaron

 

[aaron.kempf]

9.05455343543e12 or something retarded.

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

-aaron

 

[aaron.kempf]

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

 

[Harlan Grove]

(e-mail address removed) 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.

 

[Harlan Grove]

(e-mail address removed) 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!

 

[Bill Kuunders]

Whow

GO YOU, Harlan......

See what Aaron has got to say to THAT.
( :> ))

 

[Harlan Grove]

Why chance reigniting a thread like this?

 

[Mazin]

Although this is an old thread, I did come across it looking for an answer to the same question as the original poster.

The answers in this thread were mostly useless, and a couple posts were not too bad, but you were all so busy flaming each other, you just weren't getting his point.

The fact of the matter is that there is a problem with importing certain types of data into an Excel sheet. If I type 05E-112, everything is ok. But if I decide to delete the "-" character (which I had to at one point when I was working on a report for work), Excel always converted the text/numbers to scientific notation.

Nothing I did could stop it. I formatted the boxes before, after during, I tried doing the data formatting wizard path, I even tried coding a VB macro, and nothing.

So in the end, I came up with a nice simple solution. If you insert an asterisk --> ' <--- (what is in between those two arrows) before any text in the box, the box will always be treated as text.

The easiest way to do this, especially if you have a massive list of already-typed numbers, is as follows:

1- Type ' (asterisk) into the first cell of the first column in a new sheet.
2- Copy this asterisk into every cell below/next to it (lots of ways to do this, but easiest way is to drag the bottom right corner of that cell all the way to the farthest point of where your sheet will end).
3- Go to your other sheet where all your data is, and select all of it, then copy
4- Go back to the first sheet and select the area you want to paste the data (within the cells with asterisks), then right click and select "Paste Special"
5- Only change ONE thing in this pop-up box...which is to check the "Values" option in the top section
6- Click "OK"

And voila. You have an un-scientific-notationable group of cells. Never have to worry about that dam scientific notation ever again.

I hope this helps.

 

[soundkeyz]

Hi Mazin,
Very helpful post. However, I ran into a similar situation, and found that there is a small problem with this procedure. Here's what I've got:

A number, listed as "1234567890123456" will post in Excel as scientific notation; given the limits. So it shows as thus:1.23457E+15
I tried your method, and ran into one small problem. The paste special values only pasted the number over top of the apostrophe, resulting in the removal of the apostrophe. So it pasted in as scientific notation.

I then tried something different. I put an apostrophe in a cell; for the sake of this post, cell A1. I then pasted the group of numbers in a nearby row of cells. I then, in the cell I wanted the end result listed, changed the format to "TEXT" and used formula =CONCATENATE($A$1,B1) and pasted that down the row. (The $A$1 denoting a fixed cell reference for that formula for purpose of pasting.) Voila, they are now text format...except that Excel dropped the last digit and replaced it with a zero. So the end result I was looking for, "1234567890123456" came up as "1234567890123450". As this number is not for mathmatical concerns but rather is a PO#, I need to have the full number, or else the spreadsheet and its data is worthless to me.

Any thoughts? If I remember correctly, the apostrophe acts as an override to tell Excel to consider the data as text and to left justify the cell. If it considers it as text, then the 15 digit limit should not apply, correct?

 

[LeberMac]

Continually converts number data to scientific notation

Apologies to Harlan and others for the thread necromancy... BUT

I have a specific problem where this occurs daily.
I export mail data (mailing lists) into CSV files every day, and in the United States the barcode data gets generated from this string of 12 numbers.
The problem is, every time I open the excel file to make a change (like, an address contains an extra space, or there's some other minor formatting issue with the text), Excel changes the column of numbers (for the barcode) into scientific notation. If I then attempt to force the column to show up as numeric or even as "special" formatting, the number gets rounded and I lose the last couple of digits. When/if I save the file as csv, the data stays in that format instead of in the 12-number format, and the datafile is then useless. Which causes me to swear mightily at my computer in futile rage, hurling curses that cause the paint on the walls to peel and my co-workers to cover their ears.

It would be REALLY great if there were a way to disable the "automatic" Excel scientific notation conversion.

The apostrophe trick doesn't work, since that gets exported in the CSV data as well, which screws up the barcode.

So... there's REALLY no way to disable the automatic scientific notation "feature?"

--
Leebs

 

[Frances]

Well, 6 years on from the opening of this thread and now I've come by, asking a similar question to the original poster. I feel his/her pain; the Scientific Notation feature/bug has involved me in a lot of extra work. It's a pity there was so much flaming in the beginning, preventing full understanding of the circumstances.

Please let me introduce my problem by giving a simple example. I import data from Oracle into a .csv file and need to open it in Excel to manipulate further. There are six tabs with tens of thousands of rows but this two-column, five-row snippet will suffice:

"Row_ID","Text_Field"
"1","15A1"
"2","10B4"
"3","90A4"
"4","27E3"
"5","11C9"

Column two is text. The values are designations for equipment. Notice row 4 (cell B5) contains the textual value 27E3.

Save the file as a .csv and open it in Textpad, Wordpad or Notepad. The value of B5 is still 27E3.

Close the file and open it in Excel. Excel displays 2.70E+04 in the cell and 27000 in the formula bar.

Despite the double quotes around the field in the .csv.

Close the file and open it in Notepad again. The value of B5 is still 27E3.

So Excel was not displaying the actual true value in the formula bar, as I had come to expect.

The true textual value is still in the file but no matter which format I gave to cell B5, I couldn't get the value 27E3 back satisfactorily.

I did trial and error and tried Custom format with the expression ##0E-3, but whilst this made 27E3 appear in the cell, Excel still displayed 27000 in the formula bar.

Does anyone know if there is a way to prevent Excel from applying Scientific Notation, or at the least to reverse the conversion permanently for a particular file?

Thank you for reading.

Frances