Excel 2002 Formating and functions

  • Thread starter Thread starter Phillipa
  • Start date Start date
P

Phillipa

Under some circumstances I end up with a spreadsheet that
will not allow me to change the format of given cells
unless I enter each individual cell after the format
change has been made.

This also results in not being able to use formulas to
calculate numeric data ranges.

This usually happens when data has been copied and pasted
from and Access database. (When I use the Transfer option
this does not occur)

Has anyone else seen this?
 
Phillipa said:
Under some circumstances I end up with a spreadsheet that
will not allow me to change the format of given cells
unless I enter each individual cell after the format
change has been made.

This also results in not being able to use formulas to
calculate numeric data ranges.

This usually happens when data has been copied and pasted
from and Access database. (When I use the Transfer option
this does not occur)

Has anyone else seen this?
Click to expand...

Quite often, when importing data from another application, it ends up as
text rather than numeric. Now in Excel text is quite different from other
data formats. You cannot simply change the format of the cell and have the
data itself change from text. Having changed the cell format to General or
number, you also have to force the data to change - either by reentering
each cell, as you have discovered, or (for example) like this:
Copy a blank cell which is NOT formatted as text.
Then select the cells containing the data and use
Edit > Paste Special > Operation Add > OK

It's quite instructive to use a formula such as =ISTEXT(A1) to watch how
Excel is interpreting a cell whilst importing data, changing the cell format
and coaxing the data to become numeric as described above.
 
Back
Top