Assistance copying html tables into Excel for Mac

[jillfeig]

I have Office for Mac 2004, Student & Teacher Edition. I tried to copy a html table from a web site into Excel, but it did not paste correctly. Instead of giving me the two dimensional table in columns and rows like it was displayed on the web site, it put all the information into one column. I even tried to paste special, but it wouldn't allow me to choose the picture option and the transpose option made one long row.

When I've copied the same table into Excel on a Windows-based PC, it copies the table without any problems. Is it because I have the student edition? Please help.

Thanks, Jill

 

[Ric]

Hi Jill and welcome,

can you try and open the web page directly in Excel, and then copy it from there.

File -->open--> http://www.thepagewiththetableon.html

I haven't got a copy of Excel on my Mac to test it...

regards

Ric

 

[searay345]

Jill, I have Office for Students & Teachers. Post the link with the table on it and I can try to figure it out for you.

 

[jillfeig]

Thank you! Unfortunately, that URL is for Dept of Defense employees only. Let me try to find another table that's structured the same way. I'll try to copy and paste it into Excel...If I have the same problem, I will post that link for you.

Jill

 

[searay345]

Hey Jill, sounds good. Let us know how it goes.

 

[jillfeig]

Dear Ric and searay345,

I am not sure how to choose a URL, like Ric suggests. When I perform File>Open I have the various folders on my hard disk available. However, I don't see how I can ask it to open the URL instead. When I try to click the hyperlink above, Safari tells me it can't find the server.

In the meantime, I found a 3x3 table on a web site that teaches you how to make html tables (first table at top of page). When I try to copy and paste it into Excel, I get the same problem. Here's the link: http://www.yourhtmlsource.com/tables/basictables.html

 

[searay345]

Hey Jill, try this:

On the page with the table, copy all the info from the table on the webstie. Go to Excel. When you put your cursor over a cell, it should be a white cross. Hold your mouse button down and select the number of Columns and rows that the table represents. In the website you provided me, the table was three rows down and four columns accross. This totals 12 cells. Once you have all twelve cells selected (3C x 4R), then just paste the info into the cell. It should come out perfect. Let us know if you have any further questions or my explanation isn't clear.

 

[Ric]

...sounds clear to me, nice job !

 

[jillfeig]

It's not working

First I opened the web site with the table and Excel. I tried to copy the 3Rx4C table from the url (I highlighted all cells, then did the keyboard shortcut for copy, which is the Windows logo button and the 'c' on my old PC keyboard.)

Then, I put the cursor in cell A1 - it became a cross. I then held the mouse button and dragged across and down until I had 4 columns and 3 rows. Then, I right clicked and selected 'paste'. It gave me a really odd result. I got the "best film" "monsters inc" and "toy story 2" from the first row of the web table pasted as ONE column on my Excel sheet (occupying cells A1, A2, and A3)! That's it!

Did I do something horribly wrong? I tried the same with my DoD table, and I got the same bizarre result.

 

[searay345]

Hmmm....You did everything correct. It turned out perfect for me. Now I am confused. Anyone with Office Students and Teachers edition able to duplicate my results or Jill's results?

 

[jillfeig]

Sorry for the slow reply. I'm disappointed that I can't seem to get the same resulting table as you. I'll be grateful if anyone else can help. However, thank you for trying!

 

[jmay]

I also couldn't get this to work. I'm using Excel X for Mac, not the new Office 2004 version.

I found a workaround, though:

• Copy & paste from the HTML page to a text editor. That converts it to tab-delimited fields.
  
• Immediately copy & paste again into Excel.

This worked for me when I was grabbing data from here: http://tonto.eia.doe.gov/dnav/pet/hist/mg_tt_caw.htm

Cheers,
-Jason

 

[Ric]

Hi Jason and welcome,

thanks for the tip !

I'm sure it will help someone else.

regards

Ric

 

[jillfeig]

Jason - thank you for the advice. However, when I paste into the text editor, the table is pasted again as one long column, which then becomes one long column in Excel. Am I supposed to "paste special" or pre-set a preference that allows for tab delimited copying? I can't understand why my mac is the only one of the group's that won't perform this seemingly simple task! - Jill

 

[nullspirit]

Use IE, then you willbe able to paste HTML table into excel on Mac.

 

[jillfeig]

Hi guys. Thank you so much for trying to help me. Sorry I've taken so long to respond...life's been crazy-busy.

"Nullspirit" is correct - the paste function works when you use other browsers. I tried it with Netscape, and it worked perfectly.

After Jason suggested the Text Editor fix, I was really frustrated that I couldn't duplicate his work. I was so determined to figure it out, that I eventually did so! When I copy the html table and paste ino Text Editor, I have to select "Paste with current style". If I do that, I get a quasi-table that's got tabs or some kind of carriage return marks between each cell value. If I copy THAT format into Excel, it pastes correctly!

Thanks again to all of you for your help. I hope this thread can help others too.

Sincerely and best wishes, Jill

 

[searay345]

Hello Jill!

Glad to hear it's working. I used Safari and it worked fine for me. Ah, I guess it's one of those crazy computer mysteries.

Searay

 

[borismodo]

I am having trouble copying a Numbers file to my iPhone. Message tells me "spreadsheet has too many table cells".
What does this mean and how can I fix it? any ideas

 

[Kaveman]

The iPhone version of Number has a cell limit because of the limited amount of RAM and the spreadsheet you have made has to many cells