Problems with pasted HTML data
 

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?

Problems with pasted HTML data
 

Use the Value(A1) worksheet function

Or in vba use Val(str)

"Philip Davich" wrote:

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?

Problems with pasted HTML data
 

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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"medialint" wrote in message ...
Use the Value(A1) worksheet function

Or in vba use Val(str)

"Philip Davich" wrote:

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?

Problems with pasted HTML data
 

David's recommendations did the trick. Thanks for the help.



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!