Prev: how to combine several files, all with same columns, into one shee
Next: Inserting Picture Into Protected Worksheet
From: SandyC on 17 Mar 2010 11:40
when converting from xls to csv format, some of my longer mortgage numbers
get condensed into a smaller number with a letter, a plus sign and another
number. example: 100020013120 turns into 1.0002E+11. Any way to get rid
of this? Thanks,
From: Fred Smith on 17 Mar 2010 14:50
Yes, just specify the format that you want.
Format Cells...>Number>0 decimal places

Regards,
Fred


"SandyC" <SandyC(a)discussions.microsoft.com> wrote in message
news:FA52E426-360E-4BE5-B63B-1209E673CA65(a)microsoft.com...
> when converting from xls to csv format, some of my longer mortgage numbers
> get condensed into a smaller number with a letter, a plus sign and another
> number. example: 100020013120 turns into 1.0002E+11. Any way to get
> rid
> of this? Thanks,

From: Shane Carpenter on 17 Mar 2010 15:19
Make sure you double check your column widths and that they are wide enought
to fit this long number. E+11 means that the number is so large, they had
to reduce it down. To get the real number, you would have to move the
decimal spot 11 places to the right. I tried this and all you need to do is
widen your width of columns.

"SandyC" wrote:

> when converting from xls to csv format, some of my longer mortgage numbers
> get condensed into a smaller number with a letter, a plus sign and another
> number. example: 100020013120 turns into 1.0002E+11. Any way to get rid
> of this? Thanks,
From: Shane Carpenter on 17 Mar 2010 15:21
You might have to delete the number and re-enter after you make it wider
From: Joe User on 17 Mar 2010 15:45
"SandyC" wrote:
> when converting from xls to csv format, some
> of my longer mortgage numbers get condensed
> into a smaller number with a letter, a plus sign
> and another number. example: 100020013120
> turns into 1.0002E+11. Any way to get rid of this?

How did you enter and format such numbers in the first place?

I suspect they are account numbers, not values that you intend to use in
arithmetic formulas. Right?

In that case, you should have entered them as text in the first place,
either by prefixing the number with an apostrophe or by formatting the cell
as Text. Alternatively, you might have formatted the cell as Number with
zero decimal places, although that is not the best solution.

In any case, when you saved in CSV file format, the numbers lost their text
attribute because Excel does not put quotes around them :-(.

So after you open the CSV file, you must go to each cell and reapply the
Text or Number format, as you may have done before. If you apply the Text
format, press F2, then Enter to see the difference.

FYI, 1.0002E+11 is called a Scientific form. It appears when a cell is
formatted as General, but the value is wider than the column (if not the
default width) or the value is has more than 11 digits. The Formula Bar
should show all the digits.

The reason why Text format is preferred over Number format is because Excel
will modify "numbers" (numeric account identifiers) that have more than 15
digits.

 |  Next  |  Last
Pages: 1 2
Prev: how to combine several files, all with same columns, into one shee
Next: Inserting Picture Into Protected Worksheet