How to remove line breaks in Excel

Sometimes, when we scrape or copy data from the website or you receive an Excel data file from some other web source, very often you get data with some line breaks or carriage returns.

If you want to quickly remove multiple line breaks in Excel then follow this tutorial step by step. Let’s look at the dataset below and we’ll remove the line breaks from these cells by using two methods.

• Find and Replace option
• VBA code

Method 1 – Remove Line Breaks using Find & Replace option

Step 1 – Use CTRL+H shortcut key to open Find and Replace options

• Press the CTRL+H shortcut key to open up the Find and Replace options dialog box as shown below;

Step 2 – Fill in the options properly to remove Line Breaks 

• In the Find and Replace dialog box, write the followings;

Find what: 010 (from Numpad only while holding down ALT key)

Replace with: “ ” (blank space)

Note that it is important to write the number from the Numpad keys only. Otherwise, it won’t work.

Step 3 – Remove Line Breaks from the data

• After filling in the options properly, we can now press the “Replace All” button to remove Line Breaks from all data cells as shown below;

So, this is how we can remove the Line Breaks from our data using Find and Replace options.

Method 2 – Remove Line Breaks using VBA Code

Step 1 – Use ALT+F11 shortcut key to open VB Editor

• Press the ALT+F11 shortcut key to open up the VB Editor as shown below. 
• Then use the Insert option and insert a new module so that VB code can be added.

Step 2 – Insert the VB code in the Editor

• Now add the following code in the recently opened editor.

Step 3 – Insert a button in Excel from Insert Shapes

• For the ease of running the code, we need to insert a button in Excel. Therefore, go to the Insert tab on the list of main tabs.
• Click on the Shapes option from the Illustrations group. 
• Now select the Rectangle: Rounded Corners option and drag to draw the rectangle.
• Add any suitable text to name the button.

Step 4 – Assign Macro to the button

• To assign the macro to the newly added button, right click on the button and choose the Assign Macro option.
• In the next dialog box choose the macro name and press OK.

Step 5 – Run the Macro through the button

• To run the macro through the button, macros must be enabled in Excel. If macros are already enabled then click the REMOVE LINE BREAKS button to run the code.
• The code will ask you to select a range from which the Line Breaks are to be removed.
• For the sake of demonstration, only first three cells will be selected to remove the Line Breaks. When the code runs it will remove all the Line Breaks from the selected cells only as shown below;