This post will explain that how to remove line breaks in your data set in Excel. How do I remove carriage returns or line breaks with a formula in Excel. How to remove line breaks with VBA Macro in Excel. How to remove line breaks with find and Replace feature in Excel 2010/2013/2016.

When you press Alt + Enter keys on your keyboard in a Cell, it will generate a line break. And if you press multiple Alt + Enter keys on your keyboard, and it will generate multiple line breaks in your active Cell. And you may be want to remove all line breaks in a selected range in Excel. How to do it. Here are the methods to remove line breaks in your worksheet.

1. Remove Line Breaks with Formula

If you want to remove line breaks from a range of cells, you can use a formula based on the SUBSTITUTE function and CHAR function. And the formula is as below:

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

This formula will remove all line breaks in Cell B1, and replacing them with commas. And the CHAR(10) function will return a line break character.

You need to type this formula into a blank cell and press Enter key to apply this formula. And then drag the AutoFill Handle over other cells to apply it to remove all line breaks.

2. Remove Line Breaks with Find And Search Feature

You can also use Find And Search function to remove line breaks. And you can replace all line breaks with a new character or a blank character in Replace function. Here are the steps to remove line breaks in Replace function in Excel:

#1 Select the range of cells that you want to remove line breaks.

#2 go to HOME tab, click Find & Replace command under Editing group. And select Replace from the drop down menu list. And the Find and Replace dialog will open.

#3 you need to hold down the Alt key in your keyboard, and then type numbers 010 from your keyboard’s 10-keypad part. in the Find what text box. And type a character or a blank character in Replace with text box. Click Replace All button.

#4 All of the line breaks would be removed in the selected range of cells.

3. Remove Line Breaks with VBA Macro

You can also use an Excel VBA Macro to achieve the same result of removing multiple line breads in Microsoft Excel. Here are the steps to remove line breaks with VBA code:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Sub RemoveLineBreaks()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range that you want to remove line breaks", "RemoveLineBreaks", myRange.Address, Type:=8)
    For Each myCell In myRange
        myCell.Value = Replace(myCell.Value, Chr(10), "，")
    Next
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

#6 Select one Range that you want to remove line breaks. click Ok button.

#7 let’s see the result:

4. Video: Remove Line Breaks

This Excel video tutorial where we’ll explore three effective methods to remove line breaks. Join us as we dive into the first method using the SUBSTITUTE function, the second method employing the ‘Find and Replace’ feature, and the third method harnessing the power of VBA code.

https://youtu.be/dCXp-0dQgyU

5. Related Functions

• Excel Substitute function
  The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
• Excel CHAR function
  The Excel CHAR function returns the character specified by a number (ASCII Value). The syntax of the CHAR function is as below: =CHAR(number)….

See Also:

• Remove Leading and Trailing Spaces 
• Count the Number of Line Breaks