How to Remove Accidental Enter / Line Breaks in Excel Using Find and Replace

When copying data from websites, accounting tools, or ERP applications, you might notice unwanted extra line spaces inside cells. In Excel, this happens when an invisible line break (created via Alt + Enter or ASCII Char 10) gets inserted into your text.

These unintended line breaks lead to common operational errors:

• Lookup formulas such as VLOOKUP, XLOOKUP, and MATCH return #N/A errors due to hidden trailing spaces.
• Row heights expand irregularly, breaking table layout and display structure.
• Exporting sheets to text or CSV files splits single records across multiple unwanted rows.

Quick Tip: Instead of manually clearing each cell via F2 and Backspace, Excel's Find and Replace tool can strip or replace all line breaks across your entire spreadsheet in one pass.

Step-by-Step Guide: Remove Line Breaks with Find and Replace

1. Select Data:
   Highlight the target cell range or entire column. Press Ctrl + A if you wish to clean the full worksheet.
2. Open the Find & Replace Window:
   Press Ctrl + H on your keyboard to launch the Replace dialog.
3. Input the Line Break Code in "Find what":
   Click inside the Find what box, clear any existing contents, and press Ctrl + J.
   ⚠️ Note: Ctrl + J does not show standard alphanumeric text. A tiny blinking pixel or dot may appear, representing the ASCII line feed character (Char 10).
4. Set the "Replace with" Field:
   • To replace with a space: Tap the Spacebar once (useful for combining address lines or multi-word texts).
   • To remove completely: Leave the box completely blank (ideal if the line break was keyed accidentally at the end of the entry).
5. Execute:
   Click Replace All. Excel will remove all internal and trailing line breaks instantly and report total replacements made.

Formula Alternatives (Keep Source Intact)

1. Remove line breaks & trim excess spacing:

=TRIM(CLEAN(A1))

The CLEAN() function purges non-printable ASCII characters (including Char 10), and TRIM() discards redundant edge spacing.

2. Convert line breaks into regular single spaces:

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

Replaces each carriage return with a visible whitespace character.

Reference Summary

Action	Input / Setting
Open Find & Replace	Ctrl + H
Find what field	Ctrl + J
Replace with field	Leave Empty (Remove) OR single Spacebar
Run cleanup	Click Replace All

Download Test