Number fault -Help!

  • Thread starter Thread starter bm
  • Start date Start date
B

bm

When I type in a particular number namely145991652915 I get 145992E+11 on
the sheet but it still shows the correct number in the box above
Thinking it was something to do with my information on the sheet I opened a
new sheet and got the same result.
What is causing this and how can I correct it.
Blair
 
When I type in a particular number namely145991652915 I get 145992E+11 on
the sheet but it still shows the correct number in the box above
Thinking it was something to do with my information on the sheet I openeda
new sheet and got the same result.
What is causing this and how can I correct it.
Blair
Click to expand...

EXCEL 2007

Right click cell / Format Cells . . . / Format Cells window should pop
up.

Number tab / click on Number / click in Use 1000 Separator (,) / click
on OK

If possible please rate my posting.

Thanks.
 
When I type in a particular number namely145991652915 I get 145992E+11 on
the sheet but it still shows the correct number in the box above
Thinking it was something to do with my information on the sheet I opened
a
new sheet and got the same result.
What is causing this and how can I correct it.
Blair
Click to expand...

EXCEL 2007

Right click cell / Format Cells . . . / Format Cells window should pop
up.

Number tab / click on Number / click in Use 1000 Separator (,) / click
on OK

If possible please rate my posting.

Thanks.

Many thanks it worked OK. The only snag is there are commas after every 3rd
number. I can live with that
The same sheet had other numbers up to 9 nmbers . Why did it happen to the
larger number only?
Blair
 
The same sheet had other numbers  up to 9 nmbers .
Why did it happen to the larger number only?
Click to expand...

Because this is the behavior of a cell with the General format. If an
integer has more than 11 digits, General format will represent the
number in "scientific notation" -- the Scientific format. In a new
worksheet, compare the difference between entering 12345678912 and
123456789123.

By selecting the Number format with zero decimal places and without
the separator option, an integer of 15 digits or less will retain its
original appearance.

However, note that Excel will record only the first 15 digits
correctly. If the "number" is really a numeric id (e.g. serial
number) that is not intended to be used in arithmetic expressions, it
would be better to either format the cell as Text before entering the
data or type an apostrophe (aka single-quote) before the data, e.g.
'1234567890123456. This is also important if you want to preserve
leading zeros, e.g. '00012345, although the latter appearance might
also be accomplished with a careful Custom format.
 
Back
Top