Problems with pasted HTML data

P

Philip Davich

I have a user who is pasting (static) data from an HTML
table (on a website) into Excel. One column contains
numeric data, but it comes into Excel padded with spaces
on both ends (e.g. 100.00 shows up as " 100.00 "). Using
the LEN function, I can see that the length is 9. If I
apply a CODE function, I get a result of 160, which
suggests that the padded characters are indeed spaces;
however, neither TRIM nor CLEAN functions are able to
remove this data padding . Consequently, I cannot do
anything arithmetic with it. I assume this has something
to do with the fact the user is pasting the data from an
HTML table, but I'm at a loss on how to clean it up. Any
suggestions?
 
D

David McRitchie

Hi Philip,

Your spaces are probably CHAR(160) which is a
non-breaking space or   in HTML. See the TRIMALL macro
in http://www.mvps.org/dmcritchie/excel/join.htm#trimall

CLEAN is often suggested but will have no effect on CHAR(160)
as CLEAN only removes characters up to CHAR(32) so you won't
see it removing much more than TAB, LF, CR, CRLF characters.
 
Top