when I type a long number it shows up as smaller number and +

  • Thread starter Thread starter SandyC
  • Start date Start date
S

SandyC

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,
 
Yes, just specify the format that you want.
Format Cells...>Number>0 decimal places

Regards,
Fred
 
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 said:
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?
Click to expand...

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.
 
Shane Carpenter said:
You might have to delete the number and
re-enter after you make it wider
Click to expand...

If it sufficient to press F2, then Enter if the original number had 15 or
fewer digits -- i.e. it was not modified by Excel.
 
Back
Top