Google Sheets IMPORTHTML导入表格千分符误判及丢零问题

I’ve run into this exact issue with Google Sheets misinterpreting thousands separators as decimal points—super frustrating when your 7000 turns into 7! Let’s break down how to fix this:

The Root Problem #

Google Sheets uses your account’s regional settings to parse numbers. If your region uses commas as decimal separators (like many European locales), it’ll automatically treat the thousands commas in Wikipedia’s table as decimal points, mangling values like 1,650 into 1.65 (and dropping trailing zeros because they’re "irrelevant" decimals).

Solution 1: Clean Imported Data After the Fact #

First, use your original IMPORTHTML formula to get the raw table into a range (say, A1:Z):

=IMPORTHTML("https://en.wikipedia.org/wiki/Demographics_of_the_world", "table", 1)

Then, in an adjacent column/range, use SUBSTITUTE to strip out commas and convert to a proper number:

=ARRAYFORMULA(IF(ISNUMBER(VALUE(SUBSTITUTE(A:A, ",", ""))), VALUE(SUBSTITUTE(A:A, ",", "")), A:A))

This formula:

• Uses SUBSTITUTE to remove all commas from each cell
• Converts cleaned text to a number with VALUE
• Keeps non-numeric content (like headers) intact with the IF check
• Processes an entire column at once with ARRAYFORMULA

Solution 2: Combine Import and Cleaning in One Formula #

If you want to skip the intermediate step, wrap the import directly in the cleaning logic:

=ARRAYFORMULA(IFERROR(VALUE(SUBSTITUTE(IMPORTHTML("https://en.wikipedia.org/wiki/Demographics_of_the_world", "table", 1), ",", "")), IMPORTHTML("https://en.wikipedia.org/wiki/Demographics_of_the_world", "table", 1)))

The IFERROR ensures that any cells that can’t be converted to numbers (like table headers) stay as their original text, so your table structure stays intact.

Why IMPORTXML Was Messy #

IMPORTXML returns raw DOM nodes, so you’d need a much more specific XPath to target individual cells and clean them one by one—way more work than using IMPORTHTML with a simple substitution. Stick with IMPORTHTML for this use case.

Give these formulas a shot, and you should see those values like 1650 and 7000 show up correctly!

内容的提问来源于stack exchange，提问作者Luddeb123