Odd Looking Number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I am using a spreadsheet in Excel 2002 on Windows XP, for some reason the
account numbers (which someone input, along with addresses) have a little
green mark in the corner of the cell. I thought it was to show that there is
a comment, but it is not. When you click on the cell, a little warning sign
shows, saying ERROR - number in this cell formatted as text or preceded by an
apostrophe. When I click on the arrow by the warning sign, it says
Number stored as text
Convert to number
Ignore error
Edit in formula bar
Error checking options
Show formula in audinting toolbar

Now I looked at the cell, and it has no apostrophe, so I went up to Format,
Cells, Number but that had absolutely no effect when I said to convert the
cells to number format. So I click on Convert to number and what I got was
something like this - 1234567895+E2. What is going on, I was told to just
ignore it that XP does weird things to Excel. But I want to know why this
happened in the first place.
None of the other account numbers I am putting on the SS in another column
are doing this, only the ones that were input by another person( I think this
SS was originally done in Access, but why the weird numbers). Any idea on
why this happened, I do not want to chance it happening to me.
 
Numbers can either be input as numbers (in which case they act as
numbers in calculations), or as Text (in which case they are ignored or
cause errors in calculations).

The most common ways to create "text" numbers are to have the input
cells preformatted as Text, to prefix the number with an apostrophe, or
to paste from an outside source (such as a web page or Access) that
fools XL into treating the number as text.

Your 1234567895+E2 is Scientific notation for 1234567895 x 10^2.

Note that XL numbers can only have 15 digits of precision, so if you
need an account number with 16 or more digits it MUST be input as Text
or you'll lose the last digits.
 
So then if you add two 15 digit numbers together you will get an answer in
that scientific notation. How would you go about expanding the amount of
digits that Excel will allow.
 
No. The number of digits in the number and scientific notation aren't
linked. XL's display engine will default to scientific notation when the
number of input digits is too large do be displayed in the cell.

You can have a number with 1 digit in scientific notation, e.g.:

5 ===> 5E+00

50 ===> 5E+01

500,000,000,000,000,000,000 ==> 5E+20

0.05 ===> 5E-02

likewise you can format a cell to show 20 digits:

Format/Cells/Number/Custom 00,000,000,000,000,000,000

but only 15 digits of precision will be retained:

12,345,678,901,234,567,890 ===> 12,345,678,901,234,500,000

There are a couple of add-ins that claim to expand XL's precision up to
200 digits (do a Google search), but I've never used one.
 
Back
Top