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.

Select the cells you want to fix and run:

Sub fixum()
Set rr = Selection
For Each r In rr
r.Select
Application.SendKeys "{F2}"
Application.SendKeys "{ENTER}"
DoEvents
Next
End Sub
--
Gary''s Student
gsnu200712

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.

Thanks for your advice Elkar but some of the part nos have letters as part of
their designation. Also I do not understand when you say the cells are
formatted as Text if Excel is telling me they are General, why can't changing
the Cell Format change the format of the cell?

"Elkar" wrote:

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.