Vlookup using concatenated values - Excel 2000

[Craig A]

I am copying data from a number of ranges (e.g. X1, X2 and X3) into a large
range named X_Data, which I then use for looking up data with the vlookup
function. As the data in X_Data needs to be in alphanumeric order, I sort
the data once I have copied and pasted the data into the range. The problem
I am having is that the data in the first column is concatenated. I copy and
use paste special/values into the X_Data range prior to sorting. When I copy
and paste special the resulting data sometimes has an apostrophe in front.
When this occurs the vlookup does not find the data, even if I go in and
manually remove the apostrophe.

I have tried to replicate this situation in another spreadsheet and have
been unsuccessful. Any help would be appreciated.

Craig

 

[Joel]

Compare the formating in the good worksheet with the bad worksheet and check
if the cells are formated as Text or General.

You can verify the formating by clicking on the cell and then go to menu
Format - Cell - Number and verifying which format is highlighted.

The apostophe is to force the cell to be Text and you probably need a
number. General format allows Excel to determine the correct format which
sometimes causes problems like yours.

 

[Shane Devenshire]

Hi,

You can clean up the results after you do your paste special values command
by selecting and empty cell and choosing copy, then select all the cells in
the problem column and choose Edit, Paste Special, Add.