A line break is something that allows you to have multiple lines in the same cell in Excel. Below is an example of a name and address dataset wherein a single line, name and different parts of the address are separated by a line break. While it looks great in the above example, sometimes, you may not want these line breaks and have a need to remove these line breaks from the dataset. In this tutorial, I will show you three simple ways to remove line breaks in Excel (also called in-cell carriage return in Excel).
Remove Line Breaks Using Find and Replace
The easiest way to remove line breaks manually is by using Find and Replace. You can easily apply this to a large dataset and Find and Replace will do all the heavy lifting by finding all the line breaks and removing them from the cells. Suppose you have a dataset of addresses as shown below from which you want to remove the line breaks.
Below are the steps to remove line breaks using Find and Replace and replace it with a comma: The above steps would remove all the line breaks and replace these with a comma. This will give you the result in a single line in a cell in Excel.
In case you want to remove the line break and want to replace it with something else instead of the comma, then you need to specify that in Step 6 (in the ‘Replace with’ field)
Remove Line Breaks Using Formula
Another way to get rid of line breaks in Excel is by using a formula. While the Find & Replace method gives you static results, the formula will give you results that will automatically update in case you make any changes in the original dataset. Suppose you have the dataset as shown below and you want to remove the line breaks from all the cells.
Below is the formula that will do this: The SUBSTITUTE function finds and replaces the CHAR(10) character – which represents the line feed character. So, the above formula finds all the line breaks and replace these with blank – which essentially means that these line breaks have been removed. In case you want the result to be separated a comma (or any other character), you can use the below formula:
Note: In case you’ve got the Wrap-text enabled in a cell where you get the result of the formula, you may still see some text moving to the next line in the same cell. This is not because of line breaks. If you adjust the column
Remove Line Breaks Using VBA
If you’re comfortable using VBA, this could be the fastest way to get rid off the line breaks in Excel. But you got to do some work before making it a one-click operation. Below is the VBA code that will go through each cell in the The above code uses the REPLACE function in VBA to replace all the line break characters in a cell with a comma followed by the space character. The result of this VBA code is static and you can not undo this. So, if you’re planning to use it, I highly recommend you create a copy of the original dataset before using the code. You can put this code in a regular module in Excel, and in case you have to use it often and need it available in all your Excel workbooks, you can save this code either in the Personal Macro Workbook, or you can add it as an Excel Add-in. If you want to use it with a single click, you can add this macro code to the Quick Access Toolbar. This way, all you need to do is make the selection and click on the macro button in the QAT to run the code.
Line Break Vs. Carriage Return
There is a minor difference between a line break in Excel and in a carriage return. A carriage return’s function is to take the cursor at the beginning of the sentence. It was made use in the early versions of typewriters and later in computers. A carriage return by itself would not lead to a line break in Excel. When combined with Line Feed, it results in a line break. In most cases, when you see a line break in Excel, there is carriage return in it. Also, when you use ALT Enter to insert a line break in Excel, it only inserts a line feed and not carriage return. Then why should we worry about carriage return? Because you may get it in your data in case it has been downloaded from a database that uses carriage return. The ASCII code of a carriage return is 13 (CR, ASCII code 13) and you can find and replace it using formulas. You can read more about the difference between a line feed and carriage return here. I hope you found this tutorial useful. You may also like the following Excel tutorials:
How to Split Multiple Lines in a Cell into a Separate Cells / Columns How to Start a New Line in Excel Cell (Keyboard Shortcut + Formula) How to Combine Cells in Excel Separate First and Last Name in Excel (Split Names Using Formulas) How to Remove Comma in Excel (from Text and Numbers) How to Insert/Draw a Line in Excel (Straight Line, Arrows, Connectors) How to Edit Cells in Excel? (Shortcuts)
