Copy a web table into Excel without breaking the numbers

Why pasted tables break

A web page shows numbers as text. When you paste it, Excel has to guess what each cell was. It guesses wrong in a few predictable ways:

• Currency symbols and units. “₹4,250” or “12 kg” stays text, so it is left out of sums.
• Invisible characters. Pages often use non-breaking spaces between digits or around values. They look like spaces but stop Excel reading a number.
• Merged cells and stacked headers. A heading that spans three columns lands in one, and the other two are blank.
• Layout tables. Menus, footers and boxes built from tables come along with the data.
• Pages of results. Only the 25 rows on screen are copied, not the 400 behind the “Next” button.

Number and date formats

Excel reads numbers using your computer's regional settings. A page written for another region looks like text, or worse, a different number:

Dates are the dangerous case: a date that can be read both ways is converted without any warning, and only the ones that can't (like 25/04) are left as text. Check a few dates by eye after every paste.

The built-in fixes

• Excel: Data › From Web. Power Query loads tables from a page address and lets you set each column's type and locale before loading. It works well for public pages that don't need a sign-in or a click to show the table.
• Google Sheets: IMPORTHTML. =IMPORTHTML("https://…", "table", 1) pulls the first table on a public page into a sheet and refreshes it.
• After pasting: Find and Replace the currency symbol and the non-breaking space with nothing, then use Data › Text to Columns with the right column type, or NUMBERVALUE(text, ",", ".") to say which character is the decimal point.

The first two usually can't see tables that only appear after you sign in, filter or scroll, because they fetch the page on their own. The third works but has to be repeated for every table.

A quicker way

Bastion Helix Tables is a free extension for Chrome and Microsoft Edge that works on the page in front of you, including pages you are signed in to. Click its icon and a side panel lists the real data tables on the page, largest first: ordinary tables, tables inside frames and accessible data grids, with layout tables skipped. Merged cells are filled in, and stacked headers become one (“Population / Metro”).

It reads numbers, currencies (₹, $, €, £ and more), percentages and dates, including Indian 1,23,456 and European 1.234,50 formats. A column that mixes kinds of values stays as text, so nothing is silently changed. Point at the page's “Next” button once and the panel clicks through the pages, adding new rows and skipping duplicates. Then export an Excel file with typed cells, a frozen header, filters and an “About” sheet recording the source, or CSV, JSON or Markdown, or copy it straight into Google Sheets.

It reads a page only after you click its icon there, has no account and no analytics, and processes tables in your browser.

Questions

Why does Excel show numbers as text after pasting from a website?

Usually because of a currency symbol, a unit, a non-breaking space, or a thousands or decimal separator that doesn't match your regional settings. Remove those characters or convert the column with Text to Columns or NUMBERVALUE.

How do I import a table from a website into Google Sheets?

Use =IMPORTHTML("page address", "table", n), where n is the table's position on the page. It works for public pages that show the table without a sign-in.

How do I copy a table that has several pages of results?

Built-in imports only see the first page. Either change the page's “rows per page” setting first, or use a tool that clicks through the pages for you, such as Bastion Helix Tables.