2026-06-18 · Table to Sheets guide

Why your numbers import as text (and how to fix it)

You pull a table off a web page, drop it into a spreadsheet, and the numbers refuse to behave. They hug the left edge of the cell instead of the right. SUM() returns 0. Sorting puts 9 above 1,000. All three symptoms mean the same thing: your numbers are being stored as text.

The quickest confirmation is =ISNUMBER(A2). If it says FALSE, the cell is a string — and something invisible or extra almost certainly came along with the value from the HTML.

What's actually in the cell

1. Footnote markers. Reference sites are full of them: 1,234[1], 48.2[a], 5.6[note 3]. A single stray [1] turns the whole value into text, and it's easy to miss when the marker renders as tiny superscript on the page.

2. Thousands separators and currency symbols. $1,234,567 and £89,400 are formatted display strings. Depending on your locale and how the data arrives, the spreadsheet may or may not parse them — and mixed columns (some values with $, some without) tend to land as text.

3. Non-breaking spaces and trailing whitespace. HTML is littered with  , and it copies across as a character that looks like a space but isn't the one TRIM() removes. Ordinary trailing spaces from padded table cells break parsing too.

4. Locale mismatch. Much of Europe writes 4.121.581 for four million and 12,5 for twelve and a half — the exact opposite of the US convention. A sheet set to a US locale reads 12,5 as text and, worse, may silently misread 4.121 as four-point-one-two-one.

5. A leading apostrophe. '1234 is the spreadsheet's own "treat this as text" marker. It's invisible in the cell but visible in the formula bar, and it survives copy-paste.

6. The column is pre-formatted as Plain Text. If the column was set to Plain Text before the data arrived, everything you paste stays text no matter how clean it is.

Fixing it in the spreadsheet

1. Find & Replace with regex. In Google Sheets, Edit > Find and replace, tick Search using regular expressions, and clear the junk: \[\w+\] for footnote markers, [,$£€] for separators and currency, \x{00A0} for non-breaking spaces.
2. Wrap it in VALUE(). =VALUE(SUBSTITUTE(A2,",","")) in a helper column converts a cleaned string to a real number. Paste the result back as values-only.
3. Data > Split text to columns. Select the bad column and run it. Even with nothing to split on, this forces Sheets to re-parse each cell — it fixes a surprising number of text-number columns on its own.
4. Change your locale. File > Settings > Locale in Google Sheets. This is the correct fix for dot-decimal data, not find-and-replace.
5. Don't rely on Format > Number. Reformatting a cell that already holds text changes nothing. The value has to be re-entered or re-parsed.

Be honest about the European case: 4.121.581 and 4.121 are genuinely ambiguous out of context. Stripping the dots is a guess that can be off by a factor of a thousand. Set the locale to match the source instead.

Or clean it before it ever reaches the sheet

Every fix above is repair work. It's less effort to export the table clean in the first place. Table to Sheets reads the table's rendered HTML in your browser on click — no OCR, no screenshots — so cells arrive intact rather than scrambled. CSV export is free and unlimited; its Pro clean mode ($5/mo, alongside one-click export to a new Google Sheet) strips footnote markers like [1], removes comma thousands separators, and removes a leading $, £, or € from comma-grouped numbers.

Deliberately, clean mode leaves dot-decimal formats like 4.121.581 alone — that's the ambiguous case above, and a locale change is the right answer. If you already have a messy CSV and don't want an install, the same cleanup runs in the browser at dubetools.com/tools/csv-cleaner.

The short version

• Text-aligned-left, SUM() of 0, and weird sort order all mean the same thing — check with =ISNUMBER().
• The usual culprits are footnote markers, comma separators, currency symbols, non-breaking spaces, and leading apostrophes.
• Fix them with regex Find & Replace, VALUE(), or Split text to columns; reformatting the cell won't work.
• Dot-decimal European numbers need a locale change, not a strip — guessing there can be off by 1,000x.

Related guides

• Pasted a web table and it all landed in one column? Here's why
• How to copy a table from a website into Excel — without the mess
• How to export an HTML table to Google Sheets (3 reliable ways)

Related free tools