Import and convert Web text to columns in an Excel worksheet

Excel makes it easy to extract table data from a Web page — but to be of use in a worksheet, you will need to convert the text tables into columns. For example, say you need to chart U.S. GDP data from 2002 through 2006. First, you would use a Web query to import the data from the Web. Then, you’d extract the data into two separate columns. Follow these steps:

1. Open a blank worksheet.
2. Go to Data | Import External Data and then click New Web Query. In Excel 2007, click the Data tab, click Get External Data, and then click From Web.
3. Enter the Web page URL http://forecasts.org/data/index.htm (Figure A).

4. Scroll to Gross Domestic Product.
5. Click the arrow next to the Gross National Product Table. (If there are no arrows next to tables on the page, click Show Icons twice at the top of the dialog box to display them.)
6. Click the Import button (Figure B).

7. Click Existing Worksheet, if necessary.
8. Click the cell on the worksheet where you want the upper-left corner of the Web data to appear and click OK.
9. Select the data cells containing the text you want to convert.
10. Go to Data | Text To Columns. (In Excel 2007, click Text To Column in the Data Tools Group on the Data tab.)
11. Select Delimited (Figure C).

12. Click Next.
13. Click to select the Space check box under Delimiters (Figure D).

14. Click Next.
15. Click Finish.