When Commas Inside Your Data Break the Columns

Updated July 2026 · By the CSVUndo team · 6 min read

The quick answer: in a comma-delimited CSV, any field that itself contains a comma must be wrapped in double quotes — "Smith, Jr." — or the reader treats that inner comma as a column break, gives the row an extra field, and slides every value after it one place to the right. This is the quoting rule from RFC 4180, and the real fix is to make whatever produced the file quote its fields correctly. If the file is already saved with bare, unquoted commas and nothing marks the true field boundaries, re-aligning it is guesswork and you usually have to re-export. Below: the rule, why some exporters get it wrong, and what you can and can’t recover.

Why a comma inside a field splits the column

A CSV marks the boundary between columns with a comma, and on its own it can’t tell a boundary comma from a comma that belongs inside your data. So when an unquoted field — an address like 12 High St, Apt 4, or a name like Smith, Jr. — contains a comma, the reader counts that inner comma as a delimiter. The row gains an extra field, and because a spreadsheet lays out columns by position, every value after the stray comma shifts one cell right: the postcode lands under Country, the phone number under Postcode, and the rest of the row cascades out of place.

The signature is easy to recognise: most rows have the right number of columns and look fine, and only the handful that contain a comma inside a field have one column too many. That some rows only pattern is the fingerprint of embedded delimiters — a different fault from a whole file that never splits at all, which we separate out near the end.

The RFC 4180 quoting rule that fixes it

The convention that keeps a comma inside a field from breaking the column is set out in RFC 4180, the informal standard nearly every CSV reader follows. The rule is short: wrap any field that contains a comma in double quotes, and the reader treats everything between the quotes as one value, inner comma and all. Two related cases round it out — a field that itself contains a double quote, and a field that contains a line break.

So "Smith, Jr." stays a single field, and a product name like 6" cable, black is written "6"" cable, black" — inner quote doubled, whole field wrapped. Fields with none of those characters are left bare, which is why most of your rows survived and only a few broke.

What produces a file with unquoted commas

Almost always a hand edit or a home-grown exporter. Well-behaved tools already do the right thing: Excel’s own Save As → CSV, Google Sheets, and most database exports automatically quote any field containing a comma, so files straight from those sources rarely break. Trouble comes from two directions: a custom script that glues fields together with commas and never adds the quotes, and a file edited by hand in a plain text editor, where someone types a comma into an address without realising it now reads as a column break. If your broken file came from an in-house system, that exporter is the thing to fix.

Fixing a file that’s already misaligned

The order of preference is: fix the producer, not the file. If you can re-export from the source with correct quoting, do that — it is the only guaranteed-clean result, because only the source still knows where each field really ends. When the exporter is one you control, have it quote every field that contains a comma, a quote, or a line break; virtually every CSV library does this automatically if you use it instead of building the line by hand.

If you can’t re-export and only hold the damaged file, there are two situations. When the fields are actually still quoted correctly but a fussy downstream tool choked on them, re-serialising the file through a parser that understands RFC 4180 and re-writes clean quotes will fix it — that is what our tool does. But when the file was saved with bare, unquoted commas and nothing else marks the boundaries, re-aligning it is guesswork, and here we’d rather be straight than over-promise: if a row has one comma too many, no tool can know whether it belongs to the name, the address, or the notes field.

You can sometimes rescue it by hand when a known column gives the boundary away — a postcode of a fixed shape, a short list of valid country names, an ID that can only sit in one place. For a few rows that is realistic; for anything large, the reliable answer is to go back to the source and export again with proper quoting. There is no lossless automatic fix for data whose field boundaries were never recorded.

This isn’t the “everything in one column” problem

Two faults get mixed up because both look like a columns problem. If your whole file landed in a single column with commas or semicolons still visible in every cell, that is a delimiter mismatch — the reader used the wrong separator for the entire file — and the fix is in our guide on a CSV that opens with all data in one column. THIS problem is the opposite shape: the file split fine, but the few rows with a comma inside a field have one column too many. Same-looking mess on screen, different cause, different fix.

One more thing to rule out if only your very first row looks wrong: a stray invisible character at the start of the file can throw off the header, covered in the guide on the UTF-8 BOM in Excel. If none of those match, embedded commas are the culprit and the quoting rule above is the cure.

Frequently asked questions

Sources: the CSV quoting rules described here are defined in RFC 4180, available as the RFC Editor copy and the IETF Datatracker copy.