roundoff when converting text to numbers

[Guest]

Can anyone help? When I am converting text to numbers in excel, occasionally
some numbers will roundoff, especially if I am converting a number of cells
at one time. For example I may be converting the text value of 1.11 to a
number, and it rounds it to 1.1. Why is it doing this?

Also, as I am a novice, what the purpose of entering numbers as text to
begin with?

 

[JulieD]

Hi Jack

AFAIK there's no real benefit to entering numbers as text in the first
place - unless they aren't really numbers (e.g. phone numbers with a leading
0 - if you entered it directly as a number the 0 will be removed).

But if you're numbers are really numbers (e.g. 1.11) i would enter them
directly as numbers. Secondly, you didn't say HOW you were converting from
text to numbers - so i don't really know why the rounding is occuring - it
might be a display thing (click on the increase decimal icon on the toolbar
to see if the other decimals come back) or let us know how you're converting
them and this might help solve the problem

Cheers
JulieD

 

[Guest]

Julie,

To be honest, I'm trying to fix someone else's problem, and I don't know how
they entered the data. Anyhow, some of the numeric data is entered as text.
When I select a whole range of cells (in order to convert this range from
text to numeric through the use of the "copy" and "paste special" function)
and go through the proceedure to do so, I can see some of the cells rounding
the numbers as they are converted from text to numbers.

Also, is there a way when you start a new spreadsheet to format it such that
all numbers entered are numeric and not text so that this type of problem
doesn't happen??

 

[JulieD]

Hi Jack

this ties in with our other discussion - unless you want to specify
thousands separators, or decimals or currency symbol or things like that
it's not necessary to play with the cell formatting ... so if you start a
new spreadsheet off - leave the formatting alone, just type the text & enter
the numbers then AFAIK all should be fine.

by the way, are you copying a blank cell (from another worksheet/book) and
choosing your cells to convert and then choosing edit / paste special ADD -
when converting from text to numbers or are you doing something else with
paste special?

Hope this helps
Cheers
JulieD