It sounds like the data in your "bad" column is formatted as Text. The
"General" format was likely applied later, and Excel will not reevaluate Text
entries simply by changing the Cell Format. This is why when you reenter the
data, it works.

Try this to change all text values to numbers.

Enter a 1 into any blank cell
Copy that cell
Select your range of data to convert (bad column)
From the Edit Menu, choose "Paste Special"
Select the "Values" and "Multiply" options
Click OK
Delete the 1 you entered originally

That should do it. By multiplying everything by 1, anything that can be
recognized as a number, will be converted to a number without changing the
value.

HTH,
Elkar


"Ade" wrote:

I have a column of data - part nos, that Excel does not seem to properly
recognise. I can select one of these cell and copy/paste it to another cell
which works fine but if I try to use the cells as part of a MATCH (or other)
function against another column that I know has the same values in cells, it
does not seem to see this.

What is also strange is that the data in the 'bad' column is left justified
and formatted as General but if I double click one of the cells as if to edit
it and then deselect the cell by clicking on any other cell on the worksheet
the cell becomes right justified and and Excel will now recognise it enabling
functions such as MATCH to correctly work with it.

I don't understand why this is happening which is quite a problem as the
column contains a few thousand cells and it's not very efficient to have to
double click every cell before I can work with them.
I have even tried selecting all cells in the column and formatting as text
but this makes no difference.

So if anyone can tell me why this is the case or can suggest a workaround /
VBA code I (and my boss) would be very grateful.