number returns only two decimal places after I change from text

[Guest]

Dear Colleague,
When the cell is formatted for text and I change to number, it only returns
the number with two decimal places It will not change to a whole number or
number with decimals.

I tried re-loading the application and that did not work? I have Excel 2000
and sent the file to a 2002 user and it loaded fine.

I have seen in other applications from users that for some reason their
numbers have quotations marks around them and they can't do formulas. Please
help.

Thanks.

Louis

 

[Gord Dibben]

Louis

If the numbers are formatted as Text, simply re-formatting to number will not
do the trick.

Try this usual fix.

Format the cells as General then copy an empty cell.

Select the range of text numbers and Paste Special>Add>OK>Esc.


Gord Dibben Excel MVP

 

[Dave Peterson]

Changing the format to Number with 2 decimal places doesn't actually change the
value in the cell.

You could use another function to change the value, though.

if you have a formula that returns a value, you could wrap it in =round() (or
one of =round()'s several siblings).

=round(yourformula,0)

If you look at the help for round, you can click on "See Also" and see lots of
rounding functions.

=roundup()
=rounddown()
=ceiling()
=floor()
=trunc()
=int()
=mround()

Some of these are in the analysis toolpak, too. So to use =mround(), you have
to have that addin loaded (but help describes how to do that).

 

[Guest]

Dear Gord,
thanks for your reply but the whole spreadsheet has this problem, so I can't
find an empty cell to set as General then copy. Even when I open up a new new
workbook, it still opens like this. Do You have anymore suggestions. Thanks.

Louis

 

[Guest]

Dear Dave,
It doesn't work. Thanks for your reply but the whole spreadsheet has this
problem, for some reason using round() will not chage it. What do you put
between the parentehesis?


Louis

 

[Dave Peterson]

If you have a formula that's in the cell, then you put that formula in the cell.

For instance, say you had numbers in A1:A10 and in A11, you had this formula:
=sum(a1:a10)

You could replace that existing formula with:

=round(sum(a1:a10),2)

To round that sum to 2 decimal places.