Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Pasting Internet Data into Excel and Dates assumed (Excel 2K)

    Hi,

    I copy and paste some result details from the internet each week and one column on the internet shows as "8-6" or "9-7". When I copy and paste this into Excel the column entries are converted to dates and show as "8-Jun" and "9-Aug".

    Any ideas on how I can stop Excel assuming these cells are dates and just give me the strings as displayed on the internet.

    I have tried formatting the receiving column as text - no difference - the inherent format is pasted anyway I guess. Also Paste Values does not seem to work in this situation.

    Thanks in advance.

    Peter Moran

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting Internet Data into Excel and Dates assumed (Excel 2K)

    I don't know why it works this way, but if the cell is formatted as Text and if you click in the formula bar so that the cursor is located there before you paste, the value will be pasted as text. A second alternative is to type a single quote into the cell then press Ctrl+V to paste the value.
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Pasting Internet Data into Excel and Dates assumed (Excel 2K)

    Thanks for your thoughts Legare.

    Actually I paste 7 x 7 grid which includes player names and numbers, etc., with this one column which has a single digit, then a dash and then another single digit and Excel interprets it as a date.

    I have attached a jpg of the data I copy.

    Regards,

    Peter Moran

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Pasting Internet Data into Excel and Dates ass

    Can you give a link to the source data you are copy/pasting?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting Internet Data into Excel and Dates assumed (Excel 2K)

    OK, here is something else that might work for you. First, format the column as Text. Then copy the data from the web site. Open NotePad and paste the data into it. Copy the data from notepad and then paste it into Excel.
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Pasting Internet Data into Excel and Dates assumed (Excel 2K)

    Thanks again to Catharine & Legare.

    Catharine, the window does not have an address, but the site is www.ertinc.org.au. Select Ladders, Senior Ladders, Select Section, then Open Sets 5, then select the "Open Sets 5" hyperlink, select the latest date, and then select the number to the left of the match to display the window I included. You are doing well if you can follow that!

    Legare, when I copy into Notepad I get 5 lines of data, and pasting that into Excel results in each line of data going into the leftmost cell. If I paste directly into Excel from the webpage the columns and rows are maintained.

    Thanks again.

    Peter Moran

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pasting Internet Data into Excel and Dates assumed (Excel 2K)

    If you paste into Notepad, then into Excel, you can use the Data | Text to Columns wizard to split the data into separate columns. You can specify the data type for each column.

  8. #8
    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: Pasting Internet Data into Excel and Dates assumed (Excel 2K)

    If you paste it directly, you could add the formula to a blank column for example in I3)
    =TEXT(D3,"m-d")
    and then copy it down all the rows

    Then copy, pastespecial - values, the data over the columns of interest...

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Pasting Internet Data into Excel and Dates assumed (Excel 2K)

    Thanks Hans & Steve,

    Hans, good thinking - I agree the Data Text to Columns wizard would work but I tend to think of that in terms of loading files of data rather than the amount of data I have here. Will give it a go. Thanks.

    Steve, I think you would get the star award as your suggestion looks easy to do and neatly solves the problem. Thinking outside the box! Thanks.

    Thanks again.

    Peter Moran

Posting Permissions

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