Cannot format the numbers

  • Thread starter Thread starter Deborah Mowry
  • Start date Start date
D

Deborah Mowry

I am working with an excel file sent to me. It is small, but I cannot format
the numbers to currency or comma. It only shows General format in the dialog
so they have not been formatted yet.
What could have caused this to happen. I am sure the spreadsheet was
imported but I have never seen this happen before. I cannot even copy the
whole thing to another file with Paste Special and values. It still will not
allow me to format them.
I have found an answer by multiplying them by 1, but I was wondering why
this happened in the first place and is there a better solution.

Thank you
Deborah
 
this often happens when data is copied from databases. the following macro
will sort it out

Sub CellFix()
Dim cell as Range
For Each Cell in Selection
cell.value = cell.value
Next cell
End Sub

select the cells that are not working and run the macro

i can send this to you as an addin if like...
 
this often happens when data is copied from databases. the following macro
will sort it out

Sub CellFix()
Dim cell as Range
For Each Cell in Selection
cell.value = cell.value
Next cell
End Sub

select the cells that are not working and run the macro

i can send this to you as an addin if like...
 
Deborah,

Data imported into Excel generally comes in as text, instead of numbers.
Number formatting has no effect on text, even if the text consists of
numeric digits. When you multiplied them by 1, Excel "coerced" the text to
numbers on order to perform the multiplication, the result of which was
numbers. Adding 0 has the same effect.

You may have already have done the multiplication this way. Copy a 1 from a
cell, select the text, Paste special, Multiply. This does the
multiplication in place, performing the conversion to numbers.
 
Back
Top