Converting an HTML Table to Excel

Sometimes you may wish to copy a table on a web page and it put into an Excel spreadsheet. If you simply copy the table from the web page and paste it into Excel, you'll likely end up with merged cells that will misalign the data from adjoining columns. If you just paste only the text of the HTML table, you'll end up with a single column of text that can't be parsed back into tabular form very easily.

Try using the conversion tool available on this site: http://www.convertcsv.com/html-table-to-csv.htm. The tool gives you the option of either uploading an html file; entering a web page's address, or pasting in HTML code. In this example we start with a web page from the kCura site which has a lot of text, but concludes with a table at the bottom.

When I enter the web address, https://platform.kcura.com/9.3/Content/Building_Relativity_applications/Creating_an_application_in_Relativity.htm the HTML code of the web page gets copied to the box below. As you can see Step 2 of the tool gives you the choose of picking different delimiters for a .csv file.

Finally in Step 3 a preview of how the data will look in Excel is shown and you're given the option to export to a .xlsx or .csv file. A web page may contain more than one table of course, and the drop down menu gives you the chance to select the table you need.

When the exported data is opened in Excel you'll see that you have everything nicely separated in the same number of rows and columns as the HTML table, but that the conversion tool doesn't do a very good job of accounting for line breaks inside cells. In order to account for these use the Excel SUBSTITUTE formula, =SUBSTITUTE(B2," ",CHAR(10)) to switch long whitespaces between lines with a line break.