Method 1 – Using the Find & Replace Tool to Remove Line Breaks in Excel
Steps
- Select the range of cells C5:C9 where you want to remove the line breaks.
- Go to the Home tab in the ribbon and select Find & Select from the Editing group.
- Select Replace from the Find & Select option.
- A Find and Replace dialog box will appear. Press Ctrl + J in Find What. It will put a line break character in that box.
- Leave the Replace with box blank and click on Replace All.
- This will remove line breaks from your dataset.
Keyboard Shortcut
You can open the Find & Replace dialog box by pressing Ctrl + H on your keyboard.
Method 2 – Inserting the Excel CLEAN Function to Remove Line Breaks
Steps
- Select cell D5 where you want to apply the CLEANÂ function.
- Use the following formula
=CLEAN(C5)
- Press Enter to apply the formula.
- Drag the Fill Handle icon or double-click on the icon to apply this formula down column D.
Method 3 – Applying the TRIM Function to Erase Line Breaks
Steps
- Select cell D5 where want to apply the TRIM function.
- Use the following formula
=TRIM(C5)
- Press Enter to apply the formula.
- Drag the Fill Handle icon down the column or double-click on the icon to apply the formula.
Method 4 – Deleting Line Breaks by Applying the SUBSTITUTE Function
- Select cell D5 to apply the SUBSTITUTEÂ function.
- Insert the following formula in the formula box.
=SUBSTITUTE(C5,CHAR(10),"")CHAR(10) is the line break character. The SUBSTITUTE function will find all the line breaks and replace them with blanks.
- Press Enter to apply the formula.
- Drag the Fill Handle icon down the column or double-click on it to apply the formula.
Method 5 – Applying VBA Macro to Remove Line Breaks in Excel
Steps
- Open the VBA window by pressing Alt+F11.
- Go to the Insert tab and click on Module.
- A Module code window will appear. Copy the following code and paste it into the module.
Sub RemoveLineBreaks_Excel()
For Each Cell In Selection
Cell.Value = Replace(Cell.Value, Chr(10), "")
Next
End Sub- Save and close the code window.
- Select the range of cells C5:C9.
- Go to the View tab in the ribbon and select Macros.
- Select RemoveLineBreaks_Excel from the Macro Name and click on Run.
- Here’s the result.
Download the Practice Workbook
Related Articles
- How to Space Down in Excel
- How to Make Excel Go to Next Line Automatically
- How to Do a Line Break in ExcelÂ
- [Fixed!] Line Break in Cell Not Working in Excel
<< Go Back to Line Break in Excel | Text Formatting | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hi,
ive a excel sheet with a column containing multiple line breaks but if i use the “ctrl+J” to remove them they cant just be removed yet the sheet is unlocked. Can you please help on this?
Hi Mikekelly
Thanks for your query.
The ‘Ctrl+J’ command doesn’t replace the line break. It represents the line break feature when we use the Find & Replace dialog box to find this character. Now, If you look at our dataset, it was designed with multiple line breaks in a column. So, I hope that both datasets follow the same characteristic. To eliminate the line breaks from your sheet, you can go through the methods mentioned in this article. Among them, methods no 2, 3, and 4 are pretty simple and convenient. Moreover, in these methods, you do not have to deal with the ‘Ctrl+J’ command.
Let us know whether you can solve the issue. Please feel free to share with us if you have any further queries.