My supplier's prices are a thousand times wrong

Prices from a European supplier are out by a factor of about a thousand, or occasionally by a factor of a hundred. Nothing errored. The totals are simply wrong, and some individual rows look perfectly reasonable.

Why this happens

• The comma and the full stop swap jobs across locales. Most of Europe writes one thousand two hundred and thirty-four point five six as 1.234,56, and most of the English-speaking world writes it as 1,234.56. Both are ordinary, both are correct, and neither says which one it is.
• This is the dangerous class of formatting problem because both readings produce a number. A cell that fails is visible; a cell that quietly reads as 1.24 instead of 1240 is not, and it sums with everything else as though nothing happened.
• Short values are where it does the most damage. “1.240” is unambiguous to a person who knows the supplier and genuinely ambiguous to software: one thousand two hundred and forty in Munich, one and a quarter in Manchester.
• A locale setting fixes it only for files that all share one locale. The moment two suppliers send in two conventions, a single global setting is wrong for one of them, and which one it is wrong for depends on the order you opened the files.

What to do right now

• Compare a total you already know against the imported total before trusting any of it. A factor-of-a-thousand error is obvious in a sum and invisible in a row.
• Look for cells containing both separators. Those are the ones that settle the convention for the file, because only one reading of 1.234,56 makes sense, and whatever that says goes for the short values too.
• Ask the sender for unformatted output. A price column exported without grouping separators at all removes the ambiguity rather than negotiating with it.
• Do not fix this with find-and-replace across the column. Replacing every comma with a full stop turns 1,234.56 into 1.234.56, which is not a number in any locale.

Stopping it on the next file

A number field decides from the value rather than from a setting: when a cell contains both separators, whichever one appears last is the decimal mark, so 1.234,56 and 1,234.56 both arrive as 1234.56 and two suppliers writing in two conventions can land in the same column. When only a comma is present, a recognised grouping shape reads as thousands and anything else reads as a decimal comma, so “1,234” is one thousand two hundred and thirty-four and “1,5” is one and a half.

The vendor price list template is a sheet with those rules already set, if you want to see the shape of one before building your own.