Dec
3

How to remove carriage returns (line breaks) from cells in Excel 2010, 2013, 2007

In this tip you’ll find 3 ways to remove carriage returns from Excel cells. You’ll also learn how to replace line breaks with other symbols. All solutions work for Excel 2013, 2010 – 2003

3 ways to remove carriage returns (line breaks) from your text in Excel

There can be different reasons for line breaks occurring in your text. Usually, carriage returns appear when you copy text from a webpage, get a workbook that already contains line breaks from a customer, or you add them yourself using Alt+Enter.

In any case, what you want to do now is delete carriage returns since they don’t let you find a phrase and make column contents look disorganized when you turn on the wrap text option.

All these 3 ways are really quick. Feel free to pick the one that suites you best:

Note:Initially the terms “Carriage return” and “Line feed” were used in a typewriter and meant 2 different actions, you can find more at Wiki.

Computers and text processing software were created taking into consideration the typewriter specificities. That’s why two different non-printable symbols are used now to indicate line break: “Carriage return” (CR, ASCII code 13) and “Line Feed” (LF, ASCII code 10). Windows uses 2 symbols one by one: CR+LF, and LF for *NIX systems.

Be careful: in Excel you can find both variants. If you import data from a .txt or .csv file, you are more likely to find Carriage Return + Line Feed. When you break a line using Alt+Enter , Excel inserts Line Feed only. In case you get .csv files from a person who uses Linux, Unix, etc., you’ll find only Line Feeds again.

Remove Carriage Returns manually

Pros: the fastest way.

Cons: no any additional features :(.

Please find the steps for eliminating line breaks using Find and Replace:

1. Select all cells where you want to remove or replace carriage returns.
Select all cells where you want to remove or replace carriage returns.

2. Press Ctrl+H to open the Find & Replace dialog box.

3. In the Find What field enter Ctrl+J. It will look empty, but you will see a tiny dot.

4. In the Replace With field, enter any value to replace carriage returns. Usually, it is space to avoid 2 words join accidentally. If all you need is deleting the line breaks, leave the “Replace With” field empty.
Eliminating line breaks using Excel Find and Replace

5. Press the Replace All button and enjoy the result!

Delete line breaks using Excel formulas

Pros: you can use a formula chain / nested formulas for complex cell text processing. For example, it is possible to remove carriage returns and then eliminate excess leading and trailing spaces and those between words.

Or you may need to delete carriage returns to use your text as an argument of another function without changing the original cells. For example, if you want to be able to use the result as an argument of the function =lookup ().

Cons: you’ll need to create a helper column and follow many extra steps.

1. Add the helper column to the end of your data. You can name it “1 line”.

2. In the first cell of the helper column (C2), enter the formula to remove / replace line breaks. Here you can see several helpful formulas for different occasions:

  • Handle both Windows and UNIX carriage return/ line feeds combinations.
    =SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),"")
  • The next formula will help you replace line break with any other symbol (comma+space). In this case lines will not join and extra spaces will not appear.
    =TRIM(SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),", ")
  • If you want to remove all nonprintable characters from text, including line breaks:
    =CLEAN(B2)

Excel formula to delete carriage returns from cells

3. Copy the formula across the other cells in the column.

4. Optionally, you can replace the original column with the one where the line breaks were removed:

  • Select all cells in column C and press Ctrl + C to copy the data to clipboard.
  • Now pick the cell B2 and press the Shift + F10 shortcut. Then just press V.
  • Remove the helper column.

VBA macro to get rid of line breaks

Pros: Being created once, can be reused in any workbook.

Cons: you need to have the basic knowledge of VBA.

The VBA macro from the example below deletes carriage returns from all cells in the currently opened worksheet (active worksheet).

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

If you don’t know VBA really well, see How to insert and run VBA code in Excel

8 Responses to "How to remove carriage returns (line breaks) from cells in Excel 2010, 2013, 2007"

  1. Luca says:

    Just a little mistake. You wrote: (LF, ASCII code 13) instead of (LF, ASCII code 10)

    Thank you for this useful article :-)

  2. Franco says:

    I used the macro and it was really good because I don’t have the question mark in a box anymore :-) but now, the macro is deleting all the formulas I had in workbook and leaving only the answers :-(.

    • Alexander says:

      Hi Franco,

      The point is that my macro processed all the formulas on the sheet and not only those that contained a line break, my apologies. I’ve fixed the macro, please copy it anew from the article.

      If the result of the formula outputs a carriage return, I’m afraid nothing can be done about this. A macro cannot find where exactly in the formula the carriage return occurs, that is why it handles the formula’s result and not the formula per se.

  3. daniel says:

    Thanks!!!

  4. Brian says:

    I’m trying to save an excel sheet to csv and I keep getting an extra line. I tried using your VBA method and couldn’t get that to work (I’m assuming because I’m saving it to csv) and when I try the find and replace method it says no matches are found. Any other thoughts on why I keep getting an extra line?

    Thanks

Post a comment