can't format cell - have tried unlocking and unprotecting
 

I have a cell which I am unable to format. It is a time, eg 7:08. When I
select the cell and try to Format Cell, as I scroll throught the different
formats the sample does not change and when I select a format and exit the
format of the cell has not changed.
In particular, the 7:08 is positioned in the left of the cell. If I re-type
7:08 into the cell it becomes positioned in the right of the cell and I am
able to reformat it. My column has 32000 cells which have been extracted via
VLOOKUP from another spreadsheet so I do not want to have to manually
re-enter each cell so I can re-format. I am also unable to format the cells
in the original spreadsheet.

Does anyone have any ideas? The cell format is not locked and the
spreadsheet is not protected.

griffin,

This has all the classic symptoms of Excel text-itis.

Excel is treating your values as text, and applying a format doesn't change
how the cell values are treated once the text has been entered.

There are a few ways around this. One is to enter the number 1 into a cell
that is formatted as a number, copying it, and then selecting all your cells
that need conversion and useing Pastespecial, Values and Multiply.

The other is to use a VALUE function around your VLOOKUP. You could simply
change your formulas from =VLOOKUP(....) to =VALUE(VLOOKUP(....)) though
that may take some doing depending on how many different formulas you have.

HTH,
Bernie
MS Excel MVP


"griffin" wrote in message
...
I have a cell which I am unable to format. It is a time, eg 7:08. When I
select the cell and try to Format Cell, as I scroll throught the different
formats the sample does not change and when I select a format and exit the
format of the cell has not changed.
In particular, the 7:08 is positioned in the left of the cell. If I
re-type
7:08 into the cell it becomes positioned in the right of the cell and I am
able to reformat it. My column has 32000 cells which have been extracted
via
VLOOKUP from another spreadsheet so I do not want to have to manually
re-enter each cell so I can re-format. I am also unable to format the
cells
in the original spreadsheet.

Does anyone have any ideas? The cell format is not locked and the
spreadsheet is not protected.