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.
- 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.
- Consequently, all the line breaks will be removed.
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.
- Then, type the following formula in a blank cell:
=SUBSTITUTE(B5,CHAR(10),", ")
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.
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.
Steps:
- Firstly, go to the sheet containing the dataset and open the code window by clicking the View Code.
- 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
- Lastly, run the code, and line breaks will be removed.
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
<< Go Back to Carriage Return | Text Formatting | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
