Do you know what line breaks are?
When your data is being imported from salesforce or databases, you definitely face line breaks. Basically, a line break lets you have more than one line in the same cell in Excel.
Before we learn how to remove line breaks in Excel, let’s understand the concept:
With a line break, you can start a new line or allow spacing between texts in a cell. You might not want this spacing between texts that’s why you need to remove line breaks. Spacing between texts is not always needed therefore Excel has the option to remove it.
How to Remove Line Breaks in Excel With Formulas?
When you need to perform an intricate cell text process, a formula chain is helpful. For instance, you can easily remove carriage returns by removing extra leading and trailing spaces between the texts. Sometimes, you may have to remove these carriage returns so that you can use the text as an argument of another function. For instance, if you want to use the result as an argument of the function:
=lookup()
Like other features, it also has a drawback, which is the need to make a helper column while following many steps:
- Insert a helper column at the data end. You may call it 1 line.
- Put the formula to remove line breaks in the first cell of the helper column.
Below are formulas you can use for different purposes:
To handle Windows and UNIX carriage returns, the formula is:
=SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),””),CHAR(10),””)
To replace line break with other symbols such as comma + space and the formula will be:
=TRIM(SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),””),CHAR(10),”,”)
The formula for removing all non-printable characters from text and line breaks is:
=CLEAN(B2)
How to Copy the Formulas Across Other Cells in the Column?
You are free to replace the real column with the one that does not have line breaks:
For this, you can:
- Choose all the cells in column C and press CTRL + C to copy the data to the clipboard.
- Select cell B2 and press the SHIFT + F10 shortcut key. Now, press V.
- It will remove the helper column.
How to Remove Line Breaks in Excel with Find & Replace?
One of the simplest ways you can use to remove line breaks is by using Find & Replace option. When you have large datasets, this option helps by finding all the line breaks and removing them from the cells all at once. Let’s consider that you have a dataset containing addresses and you need to remove the line breaks.
Let’s follow the steps for further process:
- Choose the dataset from which you need to remove the line breaks.
- Open the Home tab.
- Choose the Find & Select option given in the Editing menu.
- Click on Replace button from the options that appear.
- Put the cursor in the Find what field box and press CTRL + J. you might not see anything but it adds the line break character in the field box.
- Put the comma in the replace field followed by a space character (,)
- Press the Replace All button.
With the above steps, the line breaks are removed and replaced with commas. Now, your result value will be in a single line.
VBA Macro to Remove Line Breaks:
With VBA macro, you can create a code that can be reused on other sheets as well. The main problem with VBA is that only experts can understand and use it.
By using the below given example, you can delete carriage returns from all cells in the active sheet.
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
Summary:
That’s it!
You are now familiar with how to remove line breaks in Excel. Just keep trying these hacks and you will understand Excel clearly.
