Fix numbers stored as text after copying a web table
September 25, 2026 · By TidyKit
A column can look numeric and still contain text. Before converting it, check what the source means: 1,250 could mean one thousand two hundred fifty or one and a quarter.
Convert a copy beside it. Compare several results with the source before replacing anything. Leave product codes and long IDs as text.
Match the separators to the source
Excel's NUMBERVALUE accepts a decimal separator and a grouping separator explicitly. These examples use English function names and commas between arguments. Your Excel may use localized names or semicolons. Microsoft documents the syntax.
| Text in A2 | Formula in B2 | Numeric result |
|---|---|---|
1,250.50 | =NUMBERVALUE(A2,".",",") | 1250.5 |
1.250,50 | =NUMBERVALUE(A2,",",".") | 1250.5 |
The source's decimal convention decides which formula to use. Do not remove every comma: that turns 12,50 into 1250 when the comma means a decimal point.
Footnote marks need a separate decision
A value such as 1250[1] includes a reference marker. Check that [1] is a citation before removing it. Brackets can also belong to a real code. Work on the amount column only, preserve the original, then convert the cleaned result.
Do not turn conversion errors into zero. A missing value and a genuine zero mean different things. Keep the error visible until you know what the source intended.
In Google Sheets, check the file's locale
Open File > Settings > Locale. The setting affects default number, date and currency formatting for the whole spreadsheet, including other collaborators. Check it in a copy before changing a shared file. Google's locale instructions.
For numeric text Sheets already understands, try =VALUE(A2) in a new column. It returns an error for unrecognized text and can also interpret dates. Use it only on the intended amount column. Google's function reference.
Choose the export that fits the data
TidyKit Table to Excel removes numeric citation marks from HTML tables. Its quick XLSX export recognizes numbers written with a decimal point and optional comma thousands separators, such as 1,250.50. Other formats may remain text. CSV and pasted values are interpreted by your spreadsheet.
After export, compare a positive amount, a negative amount, a decimal and an empty cell with the source. Keep IDs out of this conversion: leading zeros and long codes need text.