Through line breaks, the same cell has multiple lines in Excel. However, these lines are unnecessary, so you may want to remove them from your dataset. There are different ways to remove line breaks in Excel.
Using Find and Replace Method
It is considered the easiest way of removing line breaks manually in Excel. The method can easily be applied to large databases. Use the following easy steps.
1. Select the dataset that contains the line breaks you want to remove
2. Go to the Home Tab
3. Click on the Editing Group and choose to Find and Select
4. From the options that appear, select Replace
5. Position the Cursor in the Find What place and press Ctrl+ J simultaneously. This action puts the line break character in Find What field
6. You can then enter a comma then press Space
7. Select Replace All
All line breaks are hence removed and replaced with the comma. The result is a single line in a cell in Microsoft Excel.
Removing Multiple Line Breaks Using VBA Code Method
The following VBA code can be used to remove all line breaks in a worksheet.
1. Click on Developer then Visual Basic
2. You can as well press the Alt+F11 keys to open the Microsoft Visual Basic for applications window
3. Click on Insert and press Module
4. Input the VBA code
5. Press on the F5 key to run the VBA code. A prompt code appears for you to choose the range you want to remove the line breaks
6. Click OK
Removing Multiple Line Breaks using Kutools
You can also remove Multiple line breaks from Excel using Kutools. You will first need to install Kutools, then follow the simple steps below;
1. Choose the cells which you want to remove the line breaks
2. Press on Kutools
3. Go to Text Tools> Remove Characters
4. Select the Non-Printing Option; the result is shown from the Preview pane
5. Click on OK/ Apply.
The line breaks are removed from the cells which you had selected.
You can also use the formula method to remove line breaks from the database in Excel. This method is common since it gives results that automatically update in case changes are made in the original dataset. Follow the simple steps below;
1. This formula will assist you =SUBSTITUTE(A2, CHAR(10),",")
The CHAR (10) character is replaced by the SUBSTITUTE function, representing the line feed character. The formula finds all the line breaks in your database and replaces them with a blank. In case you want the line breaks to be separated by a comma, the following formula can be used; =SUBSTITUTE(A2, CHAR(10),",").
It is important to note that Excel has two functions CLEAN and TRIM. These functions automatically remove line breaks and extra spaces from your database to remove all line break from your cell, use the formula =CLEAN (B5). In this case, we are substituting the line breaks with commas, so we used SUBSTITUTE instead of CLEAN. In the above formulas, the quotes are needed since we are using a text value.