PDA

View Full Version : Spreadsheet problem



Nigel Thomson
23-08-2004, 12:41 PM
Grreeting

I have had the same problem in both excel and open office, in that part numbers in my spreadsheet are being converted to scientific form, I have posted this before and i thought I had beaten it by using open office, while it has worked much better, open office plays silly buggers if the partnumber has an 'E' in it.

example

the number 501648102E20

is displayed as

5.02E+028

and it is also written into the .csv in this format

excel does exactly the same, (except it seems to do it for a much wider range of numbers)

What I have checked

The fields are set to text not Scientific
they are imported from the original csv as "text" (and are in the correct form)

If anyone knows of a solution for either open office or excel please help

nzStan
23-08-2004, 12:45 PM
I think it has something to do with the column width. I've had the same problem with my inventory list batch files where stock codes are nine digits long.

What happen when you resize the column?

nzStan
23-08-2004, 12:47 PM
Ooops silly me, I think I didn't read your post carefully. My post would not have solved your problem....

Mike
23-08-2004, 01:07 PM
Put a single quote ' before your numbers, and the cell will be treated as text.

HTH

Mike.

Mike
23-08-2004, 01:09 PM
eg:'501648102E20Mike.

nzStan
23-08-2004, 01:14 PM
Doesn't work. When you save the file as a CSV it's written out as a Scientific format.

I've been trying a few approach to this problem but no success yet.

Only way I think we can solve this problem is by adding something in front of the number so that it doesn't fit a scientific format number.

eg instead of 501648102E20 make it SKU501648102E20

leshibbard
23-08-2004, 01:19 PM
Nigel Thomson, not from Aussie !.

Label ' no good for your example.

Have you tried the "/" and used the Lotus 123 section, this works okay.

Shows one age.

Les 3.

Nigel Thomson
23-08-2004, 01:30 PM
thanks Guys

the ' seems to work OK in open office, haven't tried it in excel yet

Les
you have lost me completely, i'm not from australia though, although I'll have a look at your suggestion should the ' not work or cause problems further on.

Thanks guys, this has been causing me to kludge things and use a multitude of programs to get a meaningful ouput

rugila
23-08-2004, 02:19 PM
>the number 501648102E20

>is displayed as

>5.02E+028

I'm not quite clear on what you want here, but
(a) "the number 501648102E20" doesn't seem to be a number, but rather some digits with text mixed in. Giving a non-number the name of "number" doesn't ipso facto make it a number.
(b) For Excel to use it as a number it has to be a format that Excel can recognize as a number.
(c) If you just want to cut out the decimal places and have Excel recognize it as 501648102E+20 (maybe not quite what you want tho) you might:

TRY THE FOLLOWING
enter it so excel should convert it to 5.02E+028
then go to format cells -->custom
and in the box 0.00E+00, delete the .00 before the E
then put in a few 000's or ###'s.
You can count how many but the resulting display should turn out to be 501648102E+20 (or similar depending on how many 0's or #'s you put in.
Excel will also treat this as a valid number for purposes of further calculation.

parry
23-08-2004, 02:41 PM
> The fields are set to text not
> Scientific
> they are imported from the original csv as "text"
> (and are in the correct form)
>
> If anyone knows of a solution for either open office
> or excel please help

Hi Nigel, Excel makes some internal data type conversions at the time data is inputted into a cell. The same prinicple applies to dates where you might enter 12/12 and this is converted to a date.

To avoid this the destination cells must be formatted as text before data is inserted into these cells. If you change the type to text afterwards, the data conversion has already occured so you will end up with the scientific notation in the cells.

As an example, in a new workbook enter 501648102E20 into a cell. You will see its converted to scientific notation. Now change the format of another cell to text then put this same value into the cell - you will see it retains its value and doesnt convert to scientific.

hth