How to Find and Remove Line Breaks in Microsoft Excel Using Find and Replace

Data imported into Microsoft Excel from web applications, CRM databases, or PDF forms frequently contains manual line breaks (carriage returns) inserted inside individual table cells. While these breaks improve visual readability for multi-line mailing addresses or product descriptions, they cause severe operational issues when sorting, filtering, exporting to CSV, or constructing lookup formulas like XLOOKUP and VLOOKUP. Excel provides a rapid technique to locate and eliminate these invisible formatting characters using the Find and Replace utility combined with a dedicated keyboard shortcut.

Understanding the Invisible Line Break Character (ASCII 10)

When users press Alt + Enter inside an Excel cell on Windows (or Option + Return on Mac), Excel inserts a line feed character corresponding to ASCII code 10. Because this character is non-printable, you cannot simply press Enter into standard search boxes. Instead, Excel requires entering a control code representation using Ctrl + J.

Step-by-Step Instructions: Removing Line Breaks with Find and Replace

To purge line breaks from a selected range or across your entire active worksheet:

1. Open your spreadsheet in Microsoft Excel.
2. Highlight the specific column or cell range containing the multi-line text (or press Ctrl + A to process the entire worksheet).
3. Press Ctrl + H to open the Find and Replace dialogue box.
4. Click into the Find what text field.
5. Press Ctrl + J on your keyboard. While the input field may appear completely blank, you will notice a tiny, blinking dot or subtle cursor shift indicating the carriage return character has been entered.
6. Click into the Replace with field:
   • Press the Spacebar once if you want to replace line breaks with a standard space (e.g., converting multi-line addresses into a single continuous sentence: 12 High Street, Suite 4).
   • Leave the field completely empty if you want to eliminate the break without adding spacing.
   • Enter a comma followed by a space (, ) to neatly delimit fragmented lines.
7. Click Replace All.
8. Excel displays a confirmation summary showing the exact number of replacements executed across your selection.

Alternative Formula Approach: Using the CLEAN and SUBSTITUTE Functions

If you prefer an automated non-destructive formula that preserves your original source column untouched, you can sanitize text strings using formulas:

Stripping All Non-Printable Characters with CLEAN

The CLEAN function automatically scans a text string and purges all low-level ASCII control characters (including line breaks 1 through 31):

=CLEAN(A2)

Replacing Line Breaks with Spaces Using SUBSTITUTE

If stripping the line break causes adjacent words to collide together without spacing, use the SUBSTITUTE function referencing ASCII code 10 via the CHAR function:

=SUBSTITUTE(A2, CHAR(10), ", ")

This formula finds every carriage return and cleanly substitutes a comma and space, maintaining proper sentence grammar.

Troubleshooting Line Breaks That Do Not Disappear

If running Ctrl + J fails to alter the cell height, ensure that Wrap Text is toggled off on the Excel ribbon:

1. Select your processed column.
2. On the Home tab of the ribbon, examine the Alignment group.
3. Click the Wrap Text button to deselect it. If the row height remains unusually tall, double-click the boundary between the row numbers on the far left to auto-fit row heights.