Currency symbols in a CSV price column: find and fix

Your product CSV has prices like "$12.50", "€9,90" or "45 TL". A shop import may expect a plain number in the price column. It can then reject those rows, skip them or read the price wrongly. The file looks fine when you open it, so the problem is easy to miss.

Why it happens. Symbols can sneak in when prices are copied from a web page, a supplier sheet or a PDF. A spreadsheet cell was formatted as currency, and the export kept the symbol in the text. Different suppliers write the currency differently, so one column mixes "$", "USD" and "TL". Some symbols are followed by a hidden or non-breaking space that is hard to see.

How to find it by hand. These steps were not tried on a real table for this post; test them on a copy first. 1. Before you sort or filter, select the whole table. If you sort only the price column, the prices separate from their SKUs and titles, and every row ends up with the wrong price. Keep a copy of the original file. 2. Look at the price column. Numbers usually sit on the right of the cell; values that sit on the left are often text with a symbol in it. 3. Filter the price column by text, or search it for the symbols you use, such as $, €, £ and ₺, and for currency codes and letters like USD, EUR or TL. 4. Search for a space inside a price; it may be a non-breaking space. 5. Note which rows are affected and whether one symbol or several appear.

If a comma in a price looks odd, that may be a different problem. See our post "Decimal comma or column separator in CSV prices?".

Fix. 1. Work on a copy of the file. 2. Use find and replace to remove each symbol, code and extra space from the price column only; do not run it on the whole sheet. 3. For spaces left behind, see "Hidden spaces in a product CSV: find and fix them". 4. If you need to keep the currency, put it in its own column. 5. Set the price column to a plain number format: a plain number with one decimal mark and no currency text. A thousands separator does not have to be removed unless your import rejects it. 6. Check that the prices are now aligned like numbers and that a sum of the column works. 7. Export the file as CSV again and open it as text to see that the prices are plain numbers.

Check. The free CSV check reads the header row and reports a missing column if it cannot find a SKU, title or price column. In the first 20 data rows it also flags price cells that contain a currency sign, letters or a space, and prices of zero or below. It reads a comma followed by up to four digits as a decimal mark, so a price such as "10,000" is not flagged; check thousands separators yourself. For a longer file, look at the rest of the price cells yourself using the steps above.