VALUE can convert recognized numeric text, but decimal and thousands separators must represent the intended amount. A string such as 1,234 is ambiguous across source conventions.
Establish the source convention
Check the originating system’s locale and sample documentation. Compare several known amounts, including decimal fractions and larger grouped numbers. Inspect the spreadsheet’s own locale separately.
Do not infer meaning from one record. A column assembled from several countries may contain inconsistent conventions that cannot be repaired safely by one global replacement.
Convert in a helper column
Try =VALUE(A2) on a known example and compare the result with the source amount. If normalization is needed, apply only an explicitly justified separator transformation before VALUE.
Blindly removing commas can turn a decimal comma into a hundredfold error. Replacing every period can similarly remove a decimal point or corrupt unrelated text. Exclude identifiers and codes from numeric conversion even if they contain only digits and punctuation.
Reconcile totals and failures
Test positive, negative, fractional, grouped, blank, and malformed inputs. Compare both individual values and a known subtotal. A non-error conversion is not enough: it may still have interpreted the scale incorrectly.
Leave ambiguous records flagged and preserve their original text. Do not turn every failed conversion into zero. For recurring imports, document the accepted source format and reject or review exceptions at that boundary. Changing the whole spreadsheet’s locale affects collaborators and other fields, so use it only when it matches the workbook’s intended conventions rather than as an unexplained workaround for one imported column.
References
Reviewed against the linked official product guidance on November 3, 2026. Features, limits, screen labels, and access can vary by account, platform, region, and organization policy.