vlookup cell format problems

[Guest]

I have a spreadsheet with a column of serial numbers comprised of both
numbers and letters. I have formatted the column to "general" and am
comparing it to another spreadsheet for matches. When I create my vlookup
formula I get partial matches. Many of the responses show #N/A but if I
double click on the original serial number column and go down the list cell
by cell hitting F2 and Enter, then the match shows up where it should have
before. I have tried several ways to ensure the columns I am comparing are
both formated as "general". Some of my columns are over 6000 rows long and I
cannot go row by row hitting F2 and Enter. It appears there is some residual
formatting hanging which is not allowing the column to be truly formatted as
"general". Any ideas how to force the formatting to "general"?

 

[waxwing]

I don't think it is related to the General format since that only
impacts the display properties of the cell. It sounds to me like the
second set of data may have been imported from another application. I
often ran into this when copying the results of an Access query into
Excel. Excel would think the numbers were really characters. There
are a couple of fixes available. I don't recall the exact fix so you
give them a try.

Perform a Copy - Paste Special/Values. (I assume you know how. The
quick version is highlight the column, pick Edit | Copy then Edit |
Paste Special | Values and press OK. Then escape to unselect the
column.

Multiple all values by 1. In a separate column, enter a column of 1s.
Highlight the first column, pick Edit | Copy, then select the column
with 1s and pick Edit | Paste Special | Multiply and press OK. Delete
the first column.

Hope that helps.

- John