Copying Web Tables To Spreadsheets

[jaygreg]

I'm trying to copy a table from a website to an Excel 2003 spreadsheet so
that the results are listed in a single line for each nursing home listed.
When I initially attempted this, I copied the table including the first
column for the check boxes. The transfer appeared to go well but before I
could examine it in depth, I chose to delete that first column of check
boxes. Then I decided that the best way to clean up the results would be to
copy a page at a time then just position my cursor at the appropriate place
at each Paste operation. Unfortunately, I've been unable to get a copy of
anything other than in the first column; everything is flowing into the
first Excel column.



I've toyed with the "Text To Column" feature but to no avail. If someone
would be kind enough to look at this State of Ohio website on nursing hoe
ratings, sort it by "Overall Rating" in the top right corner, then tell me
how to copy it to a spreadsheet, I'd appreciate it very much. URLs to the
site:

Step 1: http://www.medicare.gov/

Step 2: Click "NEW! New Nursing Home Five-Star Quality Rating"

Step 3 Click "Go To The Nursing Home Compare page"

Step 4 Click "Find and Compare Nursing Homes"

Step 5 Select "Find a Nursing Home within a County" for "Ohio - Eastern"

Step 6 Select "Summit" as the county

Step 7 Sort the results by "Overall Rating"



The objective is to get those results onto a spreadsheet that devotes one
line to each nursing home.

 

[Don Guillett]

This appears to be a bit more difficult but DOABLE. If desired, send me your
workbook along with before/after example. Send to the addy below.

 

[Shane Devenshire]

Hi,

The key is what data you select on the web page.

1. Scroll to the very bottom of the table, click and drag from the right
most cell on the bottom row of the data up and to the left. Drag to the
cell to the left of the cell containing "Nursing Home Name and General
Information". Copy and paste into Excel.

You can speed the process by starting the dragging and then using the
scrollbars to move to the top of the screen and then Shift+Clicking on the
top left cell in the table.

Even if you don't want the first column (checkboxes) include it for all the
rows of data.

It comes in fine into Excel, not need to use the Text to Columns command.
You can delete any rows or columns you don't want in Excel.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[jaygreg]

Shane, this worked. Don, thanks for your efforts as well.