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.