Pasting HTML into excel

[Don]

I have copy and pasted some HTML data (stock symbols) into excel but the
VLOOKUP function doesn't seem to recognize these symbols even though the
symbols appear to exist within the VLOOKUP search range. For example when
searching for say IBM it does not find it. If I manually clear IBM and type
IBM back into the same cell then VLOOKUP finds it.

My guess is that there are some trailing non-visible HTML characters that
VLOOKUP sees that are thwarting the match.

Any ideas how I can delete these non-visible characters?

I tried copy and paste special values, but it doesn't help.

Thanks
Don

 

[Guest]

Don . . .

My thought was to try pasting into Notepad, to see what characters are
there. This, of course, only helps if Notepad takes everything.

Also, maybe directly pasting into the Formula bar instead of the cell.

 

[Don]

I was able to paste it into notepad and then copy it back to Excel, but same
problem. I also tried Wordpad, but same result.

When I paste the cell contents into the formula bar the cursor ends two
"spaces" beyond the last letter confirming my thought that there are some
invisible characters at the end. If I backspace to eliminate those
characters VLOOKUP works as expected. Problem is there are over 2000 entries
to fix, pasting into the formula bar is just not practical.

 

[Guest]

Sounds like you need some VBA to remove the "bad" characters.

Maybe the VBA can be avoided, but offhand, I don't know of any utilities to
do this, but someone else may know . . .