Fix ’ and ’ garbled quotes in a CSV file

The text should read don’t and “quoted”. Instead you see don’t and “quotedâ€. The culprit is the curly apostrophe or quote, which word processors and websites insert automatically, and which a CSV reader has misinterpreted.

expected: don’t “hello” 2020–2024 you see: don’t “hello†2020–2024

Why it happens

The curly apostrophe ’ isn't in plain ASCII. In UTF-8 it takes three bytes (E2 80 99). If a program reads those three bytes as Windows-1252, it shows them as three separate characters: â, € and ™. Same bytes, wrong decoder. Each curly quote, dash and ellipsis produces its own sequence.

Quick lookup for the common ones:

• ’ is ’ (apostrophe)
• “ and â€ are “ and ” (double quotes)
• – is – (en dash), — is — (em dash)
• … is … (ellipsis)

Fix 1: re-import with the right encoding (best)

If the file on disk is still correct UTF-8 and only the view is wrong, nothing is damaged:

1. Open a blank Excel workbook.
2. Choose Data ▸ From Text/CSV and select the file.
3. Set File Origin to 65001: Unicode (UTF-8).
4. Load.

In Google Sheets, File ▸ Import normally reads UTF-8 correctly with no setting.

Fix 2: the garbage is already saved in the file

If someone opened the UTF-8 file in Excel as Windows-1252 and saved it, the wrong characters are now the real text. Restore them with Find and Replace (Ctrl+H), one pair at a time, from the list above. The closing double quote has a hidden extra character after â€, so searching for â€ and replacing it with ” works, but do the longer sequences (’, “, –, —, …) first or they will be broken by the shorter match.

For large files, a script is quicker. In Python, a string s that was mis-decoded as Windows-1252 can often be recovered with s.encode('cp1252').decode('utf-8'). It raises an error if the text doesn't fit the pattern, which is a useful signal that it was damaged in more than one way.

Preventing it

Save as CSV UTF-8 (Comma delimited) in Excel, not plain "CSV (Comma delimited)". Excel needs a BOM at the start of a UTF-8 file to recognise it, which the CSV UTF-8 option writes for you.

Why the problem shows up mostly in text copied from documents

Plain keyboard apostrophes (') and quotes (") are ASCII, so they survive any encoding. Curly punctuation comes from word processors, content management systems and exports that "smarten" text. That is why a column of product descriptions or comments breaks while an ID column looks fine, and why the damage often appears only in a few rows. If you control the source, you can also tell the exporting system to write UTF-8, which solves the cause rather than the symptom.

← Back to the CSV Fixer tool