Thousands separators in CSV prices: find and fix

A price like 12,500 can be read as twelve thousand five hundred, when the comma is a thousands separator, or as twelve and a half, when the comma is a decimal mark. Some programs choose one reading and others choose the other, so the same file can end up with different prices in different places.

This post covers thousands separators only. If a comma may be splitting a row into an extra column, or you still need to find out which decimal mark your target system expects, start with "Decimal comma or column separator in CSV prices?". Its steps for keeping the original untouched, confirming the target format and rechecking apply here too and are not repeated. For symbols such as $ or TL in a price, see "Currency symbols in a CSV price column: find and fix".

Find them. Look at the file as text first: a spreadsheet may already convert or reformat these values when it opens the file, depending on its language settings, and saving would write that change back. Open the CSV in a plain text editor, or import the price column as text. 1. Search the price column for a comma, a point or a space followed by exactly three digits, such as 12,500, 12.500 or 12 500. The point form is common in Turkish and many European files. 2. For each match, decide which reading you mean. Do not guess; check the source or your records.

Fix and check. 3. Rewrite the value in one style, for example 12500 with no comma, and use that same style for the whole column. Pick the style your import expects. 4. Save the CSV, open it as text again and search the column once more to confirm no unintended separators remain.

The free CSV check reads a comma or point followed by up to four digits as a decimal mark. So a price such as "12,500", "12.500" or "10,000" is not flagged, and the check cannot tell you whether you meant twelve thousand five hundred. A price with more than one separator, such as "1,250,000" or "1.250,00", is flagged as not a plain number. The check looks at the first 20 data rows only, so find thousands separators yourself with the steps above, then run the free CSV check on your file.