can't change cell format

[Laurence Smith]

Hi,
I am using Excel 2002 on my PC (Vista is the OS).

There are a number of cells with the green tick in the upper left hand
corner. I believe these are numbers that are formatted as text.

I want them to be formatted as numbers but highliting them and using
either the right click method or taking the options from the top does
not change the format.

These are numbers that have been typed directly into the cell and
transferred from other worksheets.

How do I get Excel to accept the change to a number format?

TIA

 

[Dave Peterson]

To change the format, you can select the cells and Format|cells|number tab and
choose almost anything but Text.

But that won't change the value in the cell(s).

There are lots of ways to change the values to a real number. One way is to
select the range that needs fixing. Make sure that the activecell in the
selection is one of those cells.

Then click on that warning icon (usually to the topleft of the activecell). And
choose convert to number.

The cell's format should change to general, too -- at least it did in my simple
tests (xl2003).

Another way is to copy an empty cell
select the range to fix
Edit|paste special|check Add

This also changed the number format.

If these don't work, click on that warning and verify the warning message. Then
post back with your follow-up.

 

[Daryl S]

Laurence -

If there is a range of cells that all have the green triangle in the
upper-left corner, then select the range. Move to the top-left corner near
the green triangle, and you should get what looks like a warning symbol with
a small drop-arrow next to it. Click on the drop-arrow, and it will tell you
what the condition is, and what you can do about it. If it is numbers stored
as text, then you can select to convert these to numbers (or keep as text).

 

[Laurence Smith]

Laurence -

If there is a range of cells that all have the green triangle in the
upper-left corner, then select the range. Move to the top-left
corner near the green triangle, and you should get what looks like a
warning symbol with a small drop-arrow next to it. Click on the
drop-arrow, and it will tell you what the condition is, and what you
can do about it. If it is numbers stored as text, then you can
select to convert these to numbers (or keep as text).

Thanks for the answers. I will keep them for further reference.

What I ended up doing that worked is that in each cell that showed the
green tick in the corner I entered =1*(the existing number)

This was something I'd read about a few years ago. It apparently
forces Excel to see the number as a number. Seems to work.

Thanks again for the input.