How to Remove Carriage Returns in Excel: 3 Easy Ways

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will discuss some methods to remove carriage returns in Excel; including VBA. Often, we copy data from web pages or other workbooks, which contain carriage returns, line breaks with Alt+Enter. Later, if required, we have to remove these carriage returns and line breaks. Luckily, Excel has very easy options to delete these line breaks and now we will discuss those.


How to Remove Carriage Returns in Excel: 3 Easy Ways

1. Delete Carriage Returns Using Find and Replace

We can remove carriage returns manually by Find and Replace option. For example, we have a dataset containing book names with carriage returns.

Steps:

  • At first, select the cells containing line breaks.

Delete Carriage Returns Using Find and Replace

  • Then, press Ctrl+H from the keyboard. As a result, the Find and Replace window will pop up. Now, go to Find what field and press Ctrl+J. A dot (.) will show up in the box. Keep Replace with field blank. Finally, click the Replace All button.

Delete Carriage Returns Using Find and Replace

  • Consequently, all the line breaks will be removed.

Use of Find and Replace

Read More: How to Insert Carriage Return in Excel Cell


2. Use Excel Functions to Remove Carriage Returns

Excel has some inbuilt functions to remove line breaks. In this method, we will use the combination of SUBSTITUTE and CHAR functions.

Steps:

  • At first, consider a dataset containing color names with carriage return.

Use Excel Functions to Remove Carriage Returns

  • Then, type the following formula in a blank cell:
=SUBSTITUTE(B5,CHAR(10),", ")
Use Excel Functions to Remove Carriage Returns

The SUBSTITUTE function replaces existing text with new text in a text string.  However, the CHAR function returns the character specified by the code number from the character set for your computer.

Here, the formula removes line breaks of Cell B5 and replaces those breaks with commas. On the other hand, Char(10) function returns a line break character.

  • Finally, you will get the following result. Use Autofill (+) to copy the formula to other cells.

Use of Functions

Read More: Remove Carriage Return in Excel with Text to Columns


3. Erase Carriage Returns Using VBA in Excel

If you do not want to use manual ways or formulas to remove line breaks, using VBA can be an excellent option. Suppose we have a book name dataset containing carriage returns. We will follow the below steps to delete those breaks.

Erase Carriage Returns Using VBA

Steps:

  • Firstly, go to the sheet containing the dataset and open the code window by clicking the View Code.

Erase Carriage Returns Using VBA

  • Then, write the following code to the code window.
Option Explicit
Sub RemoveCarriageReturns()

Dim MyRange As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each MyRange In ActiveSheet.UsedRange
If 0 < InStr(MyRange, Chr(10)) Then
MyRange = Replace(MyRange, Chr(10), "")
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Erase Carriage Returns Using VBA

  • Lastly, run the code, and line breaks will be removed.

Use of VBA Code

Read More: How to Find Carriage Return in Excel


Download the Practice Workbook

You can download the practice workbook that we have used to prepare this article.


Conclusion

In this article, we have discussed some easy methods to remove carriage returns in Excel. So, hopefully, these methods will help you. Download and practice our workbook for all the methods mentioned above. If you have any queries, please feel free to comment here.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hosne Ara
Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo