How to Remove Line Breaks in Excel (5 Ways)

Get FREE Advanced Excel Exercises with Solutions!

Line break in Excel means to start a new line or give spacing between text in a cell. Sometimes, you need to remove those line breaks from your worksheet. Excel gives you a platform to apply line breaks and also remove line breaks. This article will give a useful overview of how to remove line breaks in Excel. I hope you enjoy the whole article and gather some valuable knowledge along with it.


We found out 5 different ways to remove line breaks in Excel. All of the five methods are fairly easy to use. To show all five methods, we take a dataset that describes the book names of different authors.


1. Using Find & Replace Tool to Remove Line Breaks in Excel

Firstly, the easiest method to remove line breaks in Excel is by applying the Find and Replace command. In this method, there is no need to apply any formula. When you have a large dataset, this method is undoubtedly the most efficient one to use.

Steps

  • First, select the range of cells C5:C9 where you want to remove the line breaks.

Remove Line Breaks Using ‘Find & Replace’ Command in Excel

  • Now, go to the Home tab in the ribbon and select Find & Select from the Editing group.

  • Select Replace from the Find & Select option.

Remove Line Breaks Using ‘Find & Replace’ Command in Excel

  • A Find and Replace dialog box will appear. Now, press Ctrl+J in the Find What It will put a line break character in that box. Leave the Replace with box as blank and finally click on Replace All.

  • It will eventually remove line breaks from your dataset.

Remove Line Breaks Using ‘Find & Replace’ Command in Excel

Keyboard Shortcut

You can open the Find & Replace dialog box by clicking Ctrl+H on your keyboard.

Read More: Find and Replace Line Breaks in Excel 


2. Inserting Excel CLEAN Function to Remove Line Breaks

Secondly, you can remove line breaks by using the CLEAN function.

Steps

  • First, select cell D5 where you want to apply the CLEAN function.

  • Write down the following formula
=CLEAN(C5)

CLEAN Function to Remove Line Breaks in Excel

  • Press Enter to apply the formula.

  • Drag the Fill Handle icon or double-click on the icon to apply this formula down column D.

CLEAN Function to Remove Line Breaks in Excel


3. Applying TRIM Function to Erase Line Break

Another easy function to remove line breaks in Excel is the TRIM function. The TRIM function helps to reduce spacing or line breaks quite easily.

Steps

  • First, select cell D5 where want to apply the TRIM function.

  • Write down the following formula
=TRIM(C5)

Applying TRIM Function to Remove Line Breaks in Excel

  • Press Enter to apply the formula.

  • Drag the Fill Handle icon down the column or double-click on the icon to apply the formula.

Applying TRIM Function to Remove Line Breaks in Excel


4. Deleting Line Breaks by Applying SUBSTITUTE Function

The SUBSTITUTE function can be another useful function to remove line breaks in Excel. This function will substitute a line break with a comma or blank.

Steps

  • Select cell D5 to apply the SUBSTITUTE function.

  • Write down the following formula in the formula box.
=SUBSTITUTE(C5,CHAR(10),"")

Here, CHAR(10) describes the line feed character. The SUBSTITUTE function will find all the line breaks and replace them with blanks.

Remove Line Breaks Utilizing SUBSTITUTE Function

  • Press Enter to apply the formula.

  • Now, drag the Fill Handle icon down the column or double-click on it to apply the formula.

Remove Line Breaks Utilizing SUBSTITUTE Function

Read More: How to Replace a Character with a Line Break in Excel


5. Applying VBA Macro to Remove Line Breaks in Excel

Instead of using any command or functions, you can use VBA code to remove line breaks in Excel. To use VBA code, we take the same dataset including authors and book names.

Steps

  • Open the Developer tab by pressing Alt+F11. You can open it through the ribbon. If you don’t have that in the ribbon section, then you need to customize it and get the developer tab. This will open up the Visual Basic interface.

  • Now, go to the Insert tab and click on Module.

Embedding VBA Code to Remove Line Breaks

  • A Module code window will appear. Copy the following code and paste it.
Sub RemoveLineBreaks_Excel()
For Each Cell In Selection
 Cell.Value = Replace(Cell.Value, Chr(10), "")
Next
End Sub
  • Close the code window.
  • Now, select the range of cells C5:C9.

  • Go to the View tab in the ribbon and select Macros.

  • A Macro dialog box will appear. Select RemoveLineBreaks_Excel from the Macro Name and click on Run.

Embedding VBA Code to Remove Line Breaks

  • Here, we have our desired result which removes all the line breaks.

Embedding VBA Code


Download Practice Workbook

Download this practice workbook


Conclusion

Line breaks are necessary in some cases in Excel and at the same time, you need to eliminate those line breaks after your work. This article has shown you the five most useful ways to remove line breaks in Excel. I hope you enjoy the article thoroughly.


Related Articles


<< Go Back to Line Break in Excel | Text Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

2 Comments
  1. 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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo