LineBreaks

Remove line breaks in Excel and CSV

A CSV file has two kinds of line break and they are the same bytes. One separates records; the other sits inside a quoted cell because somebody pressed Alt+Enter. Removing both turns your sheet into one row. The tool below opens in CSV mode, which only touches the breaks inside quoted fields.

Every break disappears and the text becomes one paragraph.

6 lines · 113 characters · 12 wordsCRLF ×2LF ×3
3 lines · 113 characters+0 characters

Two CSV records whose address cells contain manual line breaks. The record separators are CRLF; the breaks inside the cells are LF.

The rule that makes this solvable

RFC 4180 is the closest thing CSV has to a specification, and two of its rules are all you need. Rule 1: “Each record is located on a separate line, delimited by a line break (CRLF).” Rule 6: “Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes.” Both quoted from the RFC on rfc-editor.org, read directly.

Put together, they say that a line break is a record separator unless it sits between a pair of double quotes — in which case it is data. So the file is not ambiguous at all; it is only ambiguous to a program that splits on line breaks without tracking quotes. That program is the one every "convert CSV" snippet contains.

What a naive split gets wrong

Seven CSV files, each written the way the RFC specifies: CRLF between records, and — where a cell has a manual break — a break inside the quotes. Column two is how many records text.split(/\r?\n/) believes there are. Column three is how many there actually are.

Generated at build time by running both splitters over each file. ␍␊ is a CRLF record separator; ␊ inside quotes is a break inside a cell.
File Naive records RFC 4180 records With CSV mode on With CSV mode off
id,name␍␊1,Ada␍␊2,Grace
Three ordinary records, nothing quoted. 		3 3 id,name␍␊1,Ada␍␊2,Grace
3 records kept 		id,name·1,Ada·2,Grace
1 record left
id,address␍␊1,"12·Long·Road␊Springfield"␍␊2,"Elsewhere"
One cell holds a manual line break. 		4 wrong 3 id,address␍␊1,"12·Long·Road·Springfield"␍␊2,"Elsewhere"
3 records kept 		id,address·1,"12·Long·Road·Springfield"·2,"Elsewhere"
1 record left
id,note␍␊1,"first␊second␊third"
Two breaks inside a single cell. 		4 wrong 2 id,note␍␊1,"first·second·third"
2 records kept 		id,note·1,"first·second·third"
1 record left
id,quote␍␊1,"she·said·""hello""␊and·left"
A quoted cell that also contains a doubled quote. 		3 wrong 2 id,quote␍␊1,"she·said·""hello""·and·left"
2 records kept 		id,quote·1,"she·said·""hello""·and·left"
1 record left
id,name␍␊1,"Lovelace,·Ada␊Countess"
A quoted cell containing a comma and a break. 		3 wrong 2 id,name␍␊1,"Lovelace,·Ada·Countess"
2 records kept 		id,name·1,"Lovelace,·Ada·Countess"
1 record left
id,note␍␊1,"line·one␍␊line·two"
The break inside the cell is itself CRLF, as RFC 4180 writes it. 		3 wrong 2 id,note␍␊1,"line·one·line·two"
2 records kept 		id,note·1,"line·one·line·two"
1 record left
id,name␍␊1,Ada␍␊
A file that ends with a record separator. 		3 3 id,name␍␊1,Ada␍␊
3 records kept 		id,name·1,Ada
1 record left

The naive count is wrong on 5 of the 7 files — every one that has a break inside a cell, which is every file anybody has this problem with. CSV mode preserved the record count on all 7 of them; the same transformation with CSV mode off collapsed 7 of 7 into a single record, because without the quoting rule there is nothing to tell it to stop.

Note the fourth file in particular. It contains ""hello"" — a doubled quote, which RFC 4180 defines as one literal quote character inside the field, not as the field ending and another starting. A quote-tracking splitter that misses that escape rule flips its idea of "inside" and "outside" for the whole rest of the file, and the damage is silent.

What we did not test

Excel itself. We had no copy of Excel to test against, so this page makes no claim about which character code Excel writes for Alt+Enter, what its CSV export does with a cell that already contains a CRLF, or how it round-trips a file. The only Microsoft documentation this page cites is the CHAR function page, which notes that Excel for the web supports CHAR(9), CHAR(10), CHAR(13) and CHAR(32) and above — and it gives no line-break example at all.

That is not a gap you have to accept, because you have a measuring instrument: paste one of your own cells above and press Show invisible characters. The badge names exactly which character your build of your spreadsheet put there, on your machine, today. A table of what some other version did on some other machine would be worth less.

Doing it without leaving the spreadsheet

If you would rather not round-trip through a text tool, the in-sheet approach is a SUBSTITUTE against the character code. Find out which code you have first — the reveal view above will tell you — and then substitute that one: 

=SUBSTITUTE(A1, CHAR(10), " ")     the LF case
=SUBSTITUTE(A1, CHAR(13), " ")     the CR case
=TRIM(SUBSTITUTE(SUBSTITUTE(A1, CHAR(13), " "), CHAR(10), " "))   both, tidied

The nested form matters for the same reason the whole site exists: if the cell holds CR + LF and you substitute only one of them, the other stays, invisible, and every comparison against that cell quietly fails. TRIM on the outside collapses the doubled spaces the substitution leaves.

The find-and-replace dialog is the other route. Its search box does not accept a typed line break, which is the reason the question gets asked at all; the substitution formulas above sidestep the dialog entirely.

Going the other way

Two related jobs come up constantly and both live elsewhere on this site. To turn a column of cells into one delimited string — for a formula, a query, or a mail merge — use join lines, which can also wrap each value in SQL or JSON quoting. To fix the line endings of a file a spreadsheet wrote so that a script can read it, use convert line endings.

A short checklist

  1. Save or copy as CSV, not as a formatted paste — formatted pastes lose the quoting that makes this solvable.
  2. Paste, and read the badge counts. CRLF between records and LF inside cells is the normal picture.
  3. Leave CSV mode on. It is the only reason your rows survive.
  4. Check the result's record count against the original before pasting it back.

Cells, rows, and the breaks between them

How do I get rid of the line breaks inside spreadsheet cells?

Export or copy the sheet as CSV, paste it above with CSV mode on, and the breaks inside quoted cells are replaced while the record separators between rows survive. Paste the result back. The alternative that stays inside the spreadsheet is a SUBSTITUTE formula against the character code, which the section below explains.

Why did my CSV explode into hundreds of rows?

Because something split it on every line break rather than only on the ones between records. RFC 4180 puts line breaks inside quoted fields, and they are the same bytes as the ones between records — only the quoting tells them apart. Of the 7 sample files below, a naive split gets 5 of them wrong.

Which character does a spreadsheet put in a cell for a manual line break?

Microsoft's own documentation for the CHAR function notes that Excel for the web supports CHAR(9), CHAR(10), CHAR(13) and CHAR(32) and above — but it gives no line-break example, and we could not test Excel here, so this page does not tell you which code your file uses. Paste a cell above and press Show invisible characters: the badge names it exactly, whichever it turns out to be.

What does CSV mode actually do?

It walks the text tracking whether it is inside a quoted field, following RFC 4180's rule that a doubled quote inside a field is a literal quote rather than the end of it. Text inside a field goes through the chosen mode; text outside a field, including every record separator, is copied through untouched.

Will it break my quoted commas?

No. Commas are never touched by any mode here — this tool works on line breaks and whitespace. The quoting is only read, never rewritten, so a field containing Lovelace, Ada comes back with its quotes and its comma exactly as they were.

My file uses semicolons instead of commas. Does that matter?

Not for this. Record separation and field quoting are what decide where a line break is safe to remove, and neither depends on the field delimiter. A semicolon-delimited file with quoted fields is handled identically.

Can I go the other way and turn a column into one cell?

Yes — that is the join lines mode. Paste the column, pick a separator, and you get a single line back. If the destination is a row across, choose Tab as the separator.

Once the cells are clean

  • the line break remover with every mode exposed — when the file is not really a CSV — a log, an export, a wall of pasted text — paste it there instead and let the badge strip decide which mode you need.
  • Join lines with a separator — turning a column of lines into a comma list, a SQL IN clause or a JSON array.
  • Convert line endings — the byte-level conversion table for LF, CRLF and CR, with the round-trip check.
  • CRLF vs LF — where the two-character line ending came from, and what git actually does to your files under each core.autocrlf setting.
  • Word Counter — the other half of the tidy-up job: word, character and reading-time counts once the text is one paragraph again.
  • Case Converter — text pasted out of a PDF often arrives SHOUTING as well as broken — this fixes the capitalisation under four style guides.