Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
griffin
 
Posts: n/a
Default 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.
  #2   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 02:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"