How to remove line breaks in Excel cells

Swap CHAR(10) for a separator

A line break inside a cell, the kind you get from Alt+Enter, a web form or a CRM export, is the character CHAR(10). SUBSTITUTE finds every one of them and replaces it with whatever you choose. For addresses, a comma and space reads naturally and keeps the parts apart.

Replacing with nothing ("") is rarely what you want: the lines run together, so "Leeds" and "LS6 2AB" become "LeedsLS6 2AB". Always replace with a space or a separator.

Replace with a space, then TRIM

Real data has stray spaces next to the breaks and empty lines between paragraphs. Priya's address has a space before its first break, and her delivery note has a blank line in the middle. Replacing each break with a space then leaves double spaces.

TRIM fixes that: it removes spaces at the start and end and squeezes runs of spaces down to one. Note that TRIM on its own does not remove line breaks, only spaces, so you need SUBSTITUTE first.

CHAR(13): the break that stays behind

Text from some Windows systems and databases ends each line with two characters: a carriage return, CHAR(13), and then the line feed, CHAR(10). Remove only CHAR(10) and the carriage returns stay in the cell. They are invisible or show as a small box, but they break lookups, make LEN too long, and turn up as strange gaps when you paste the text elsewhere.

Tom's address came from such an export. Remove CHAR(13) first, then swap CHAR(10) for the separator. If you are not sure which kind you have, this version is safe on both.

CLEAN: removes breaks, but glues the words

CLEAN deletes the non-printing characters 0 to 31, which includes both CHAR(10) and CHAR(13). It is the quickest way to get rid of every kind of break and other control characters from old systems. Its catch is that it deletes them with no space in their place.

The fix is to combine the tools: SUBSTITUTE turns the line feeds into spaces, CLEAN removes the carriage returns and any other control characters, and TRIM tidies the spacing. This is the formula to pick for messy exports when a single-line result is what you need.

Find and Replace with Ctrl+J (no formula)

To clean the cells in place without a helper column, use Find and Replace. Ctrl+J types a line feed into the Find what box. The box looks empty afterwards (you may see a tiny blinking dot), but it holds the break. This only finds CHAR(10); if your data also has CHAR(13), use the formula in the CHAR(13) section instead.

1. Select the column, for example B2:C5. If you select a single cell, Excel searches the whole sheet.
2. Press Ctrl+H to open Find and Replace.
3. Click in Find what and press Ctrl+J once.
4. In Replace with, type a space (or a comma and a space).
5. Click Replace All. Then turn off Home > Wrap Text if you no longer need it, and double-click a row border to fit the height.

Find the cells that still have breaks

Before and after cleaning, count the cells that contain a line break. FIND returns a position when it finds CHAR(10) and an error when it does not, so ISNUMBER turns that into TRUE or FALSE, and SUMPRODUCT counts the TRUEs.

To count the lines in one cell, compare lengths: =LEN(B2)-LEN(SUBSTITUTE(B2,CHAR(10),""))+1 gives 3 for Nadia's address. To keep only the first line, use =TEXTBEFORE(B2,CHAR(10)) in Excel 365.