why won't a cell accept formatting

[Guest]

I have a spreadsheet that was exported from an oracle database. If I try to
sort by a number it acts as though it is formatted as text. (ie text sort =
1,10,11,20,24,3,33,4,47,... vice number sort = 1,3,4,10,22,20,24,33,47).
When I try to set the format category as "Number", Excel says it's
catagorized as a number, but it still acts as if its a "text" format. Why?
The only way I can get the formating to "take" is if I enter the cell as if I
was going to edit it. I don't want to have to do that for thousands of
cells. Why does this happen?

 

[Guest]

Sometimes when data is imported from an external source, it may be manually
stored as text. This means that an apostrophe has been placed in front of
each data value. The apostrophe is invisible on the spreadsheet, but can be
seen in the formula bar. The apostrophe overrides any cell formatting that
may be applied and treats the value as text.

If this is the case with your data, then here's and easy way to remove all
of the apostrophes.

In any unused cell, enter the number 1.
Copy that cell
Select your range of data
Select "Paste Special"
In the options, select "Multiply"
Click OK

This will multiply all of the cells in your selected range by 1 thus
converting the value to a number if possible. You can then delete the 1 that
you entered earlier.

HTH,
Elkar