can't format cell - have tried unlocking and unprotecting

[Guest]

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.

 

[Bernie Deitrick]

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