Hi All,

I copied three columns (City, State, Zip) from a web site which I
presume is HTML to a blank Excel 2007 sheet. I performed a copy past
special past values to a second sheet to get rid of any hyper links or
formatting.
Now here's where it get's tricky.
Assuming these three columns are A, B & C, I made a VLOOKUP in
column D.
I referenced column C (zip) to a table on another sheet which had
supplementing information.
The VLOOKUP returned an #N/A.
Now if I double click one of the zip codes in column C and then hit
enter, the VLOOKUP will work. Why won't it work if I DON'T do this?
I have thousands of these numbers so I need a way to do this in mass.

Any ideas?

Thanks for your help in advance.

I've run into this too. Try running a VALUE(cell ref) command on the first
zip code and then copying the data down. You can then copy and paste as
values back into the original data column.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"lehigh46" wrote:

Hi All,

I copied three columns (City, State, Zip) from a web site which I
presume is HTML to a blank Excel 2007 sheet. I performed a copy past
special past values to a second sheet to get rid of any hyper links or
formatting.
Now here's where it get's tricky.
Assuming these three columns are A, B & C, I made a VLOOKUP in
column D.
I referenced column C (zip) to a table on another sheet which had
supplementing information.
The VLOOKUP returned an #N/A.
Now if I double click one of the zip codes in column C and then hit
enter, the VLOOKUP will work. Why won't it work if I DON'T do this?
I have thousands of these numbers so I need a way to do this in mass.

Any ideas?

Thanks for your help in advance.

Thank you so much. It worked like a charm.
I've been trying off & on to find an answer to this for years.
You're the best.





On Tue, 22 Jul 2008 10:22:04 -0700, M Kan <tipsoftheweek at gmail dot
com wrote:

I've run into this too. Try running a VALUE(cell ref) command on the first
zip code and then copying the data down. You can then copy and paste as
values back into the original data column.

Paste Values means nothing when you are not copying from Excel. If the
webpage is not set up properly, you will always need to reformat and
possibly delete some characters (like non-breaking spaces) before
being able to use the data properly. If the copy job is frequently
done, I find it easiest to put all the steps into a macro and run it
after pasting.