Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to calculate Pasted HTML | Excel Discussion (Misc queries) | |||
Pasted HTML Table Data Won't Convert From Text | Excel Discussion (Misc queries) | |||
Unable to delete html data pasted into a cell | Excel Discussion (Misc queries) | |||
pasted HTML text disappears | Excel Discussion (Misc queries) | |||
HTML Table pasted to Excel | Excel Discussion (Misc queries) |