Excel Tables to HTML - helpful hints for converting Excel Spreadsheets to HTML Tables without all the extra baggage that Excel inserts when saving to HTML.

Important First Things

  1. Make sure that the Excel Spreadsheet contains only the information that you want in your table. Remove everything before and after that doesn't fit into the row/column table format.
  2. Save the Excel Spreadsheet as a tab delimited text file (.txt).

The Conversion

  1. Open the new .txt file in MS Word. It should look like this with tabs between data elements:
            Data-1   Data-2   Data-3
            Data-4   Data-5   Data-6
  2. Use Word's Replace Function to Replace All tabs with table data tags:
        Find what:      ^t
        Replace with: </td><td>
    The file now looks like this:
            Data-1</td><td>Data-2</td><td>Data-3
            Data-4</td><td>Data-5</td><td>Data-6
  3. Use Word's Replace Function to Replace All line endings with end and begin table row tags:
        Find what:      ^p
        Replace with: </td></tr>^p<tr><td>
    The file now looks like this:
            Data-1</td><td>Data-2</td><td>Data-3</td></tr>
            <tr><td>Data-4</td><td>Data-5</td><td>Data-6</td></tr>
            <tr><td>
  4. Add the following to the beginning of the file:
        <table border="1" cellpadding="3" summary="data">
        <tr><td>Data-1</td><td>Data-2 ...
  5. Replace the <tr><td> on the last line with:
        </table>
    The final properly formatted HTML Table now looks like this:
            <table border="1" cellpadding="3" summary="data">
            <tr><td>Data-1</td><td>Data-2</td><td>Data-3</td></tr>
            <tr><td>Data-4</td><td>Data-5</td><td>Data-6</td></tr>
            </table>

Final clean up

Excel puts quotation marks around any data elements containing commas or quotation marks and changes all single quotation marks into double quotation marks. Also, ampersands are a special charcter and should be replacesd with the HTML encoding. This clean-up makes sure that these data elements are properly handled.

  1. Temporarily Replace All doubled (embedded) quotation marks with tildes (~):
        Find what:      ""
        Replace with: ~
  2. Replace All single quotation marks with nothing (delete them):
        Find what:      "
        Replace with:
  3. Replace All tildes with single quotation marks (restore embedded):
        Find what:      ~
        Replace with: "
  4. Replace All ampersands with the HTML encoding:
        Find what:      &
        Replace with: &amp;

When embedded in your HTML page the table will look like this:

Data-1 Data-2 Data-3
Data-4 Data-5 Data-6

Copyright © 2002 by J. E. Rickenbacker. All rights reserved.

4-9-05