Cannot Format Excel Cells

[Will]

Hi -

I have an excel spreadsheet that is being generated by a
data dump from SQL Server through DTS. The spreadsheet
contains approximately 21,000 rows of 129 columns (mostly
short text, some numbers).

My problem is that users are attempting to use this
spreadsheet to perform calculations, pivots, etc and are
unsuccessful. When trying to change the format of a
cell / column it appears to have no effect.

For example, I can highlight a row of numbers, change the
format from General to Currency with 2 decimal places and
click OK. The data does not change at all (i.e. the $ is
not added and the decimal point precision is not changed)
however when I open the cell / column format, it is still
selected as Currency with 2 decimal places. The
spreadsheet is not protected / locked, no formulas, no
macros, no permissions issues, just a basic spreadsheet
with lots of data.

Any thoughts, suggestions, etc are greatly appreciated.

Thanks.

 

[Don Guillett]

try
Sub ConvertThem() 'Harald Staff
Dim C As Range
For Each C In Intersect(Selection, _
ActiveSheet.UsedRange)
If Not C.HasFormula Then
If IsNumeric(C.Value) Then
C.Value = C.Value * 1
End If
End If
Next
End Sub

 

[Gord Dibben]

Will

This is usually a result of the cells being formatted as Text. Just changing
the format does not work.

Copy an empty cell then select all cells and Paste Special>Add>OK>Esc. This
move changes the "numbers" to real numerics. Does not affect the real Text
cells.

Try your currency format again on the numeric cells. Should work.

Gord Dibben XL2002