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.
- Remove Line Breaks with Formula
- Remove Line Breaks with Find And Search Feature
- Remove Line Breaks with VBA Macro
- Video: Remove Line Breaks
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.
Table of Contents
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.
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 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.
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:
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)….