Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Copying From A Web Page (2000)

    I am trying to copy/paste numbers from a web page. When I do a plain vanilla copy/paste, the numbers are copied with either leading or trailing spaces, so that Excel doesn't recognize them as numbers. When I try to do a copy/paste special, I get all fouled up. Any help? There are > 100 numbers that are then referenced by other worksheets (>10).
    Thanks,
    Jeff

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying From A Web Page (2000)

    Of course you could always do a search and replace for a space, replacing with nothing...

    What happens if you open the webpage in Excel (File, open, type the url)?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copying From A Web Page (2000)

    I tried the search and replace technique, and it still leaves trailing spaces. I'm not sure I know what you mean by your second comment.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copying From A Web Page (2000)

    Use the TRIM function or perhaps parsing depending on how it is formatted.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copying From A Web Page (2000)

    What is parsing?

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying From A Web Page (2000)

    My second comment should be an alternative from copying from IE and pasting into Excel: Open the webpage in Excel itself, rather than in IE. Then copy the data you need from the Excel sheet into the place you need them.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copying From A Web Page (2000)

    Jan,
    I tried that but it still came up as a non-Excel page. Any help?

  8. #8
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying From A Web Page (2000)

    Have you tried cleaning them up with the trim() function?

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying From A Web Page (2000)

    Could you post the resultant page (as opened into Excel) here (and the url if it is not confidential)?

    My way of doing this would be to have an "import" page that contains the copy of the webpage after reading it into Excel and to have another page with formula's (like the TRIM function suggested by BAT17) which make sure you extract the data.

    That method ensures you have to do no parsing/search-and-replacing, etcetera. Just plain and vanilla copying and pasting.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copying From A Web Page (2000)

    It is over 355,000 bytes. Here is the URL: http://www.futuresource.com/quotes/quotes....x=6&image53.y=6. The column I need to link is K. I have no problem searching and replacing the "y", but it is the trailing spaces in the first few cells. I realize there are only a few, but at times there can be in excess of 30-40.

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying From A Web Page (2000)

    I opened this webpage in Excel using this little line of code:

    workbooks.Open "http://www.futuresource.com/quotes/quotes.asp?fmt=s&type=future,index&symbols=ng&imag e53.x=6&image53.y=6"

    It looks OK to me, but I don't know what part of the sheet you need copied.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copying From A Web Page (2000)

    Yes-sorry, I was able to eventually open it, but see my prior post re formatting, etc. the trailing spaces.
    Thanks

  13. #13
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying From A Web Page (2000)

    Since the info there is in a table, you might think about a web query....Data/Get External Data/New Web Query. Fairly neat things you can do betwixt Excel and the Web now.

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying From A Web Page (2000)

    There are characters in there that resemble spaces, but aren't (they are ASCII Code 160, not 32).

    I selected a cell with a number, selected the first character, pressed control-c.

    Then control-h (edit/replace), control-v in the search box, click All.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying From A Web Page (2000)

    Good suggestion!. Just did Data, Get external data, New Web query, pasted in the url, accepted the defaults and got a nice page with numbers without any characters around them. (are you listening jlkirk?)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •