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

This tutorial will introduce you to three ways to remove carriage returns from cells in Excel. You will also learn how to replace line breaks with other characters. All suggested solutions work in Excel 2013, 2010, 2007 and 2003.

Line breaks can appear in text for various reasons. Usually carriage returns occur in a workbook, for example when text is copied from a web page, when they are already in a workbook received from a client, or when we ourselves add them by pressing keys. Alt + Enter.

Whatever the reason for them, the challenge now is to remove carriage returns, as they interfere with phrase searches and result in column clutter when wrapping is enabled.

All three presented methods are quite fast. Choose the one that suits you best:

  • We remove all line breaks manually to quickly put things in order on one sheet.
  • We remove line breaks using formulas and thus set up complex processing of text data in cells.
  • We use a VBA macro to clear line breaks from several workbooks.

Note: Initially, the terms “Carriage return” and “Line feed” were used when working on typewriters and denoted two different operations. An inquisitive reader can independently find detailed information about this on the Internet.

Computers and word processing software were designed with the characteristics of typewriters in mind. This is why two different non-printable characters are now used to indicate a line break: carriage return (Carriage return, CR or ASCII code 13) and Line translation (Line feed, LF or ASCII code 10). On Windows, both characters are used together, and on *NIX systems, only newlines are used.

Be careful: Both options are found in Excel. When importing from files .txt or . Csv the data usually contains carriage returns and line feeds. When a line break is entered manually by pressing Alt + Enter, Excel only inserts a newline character. If the file . Csv received from a fan of Linux, Unix or other similar system, then prepare for an encounter with only a newline character.

Removing carriage returns manually

Pros: This method is the fastest.

Cons: No extra perks 🙁

This is how you can remove line breaks using the “Find and replace“:

  1. Select all cells where you want to remove carriage returns or replace them with another character.How to remove line breaks (carriage returns) from cells in Excel 2013, 2010 and 2007
  2. Press Ctrl + Hto bring up a dialog box Find and replace (Find and Replace).
  3. Put the cursor in the field To find (Find what) and press Ctrl + J. At first glance, the field will seem empty, but if you look closely, you will see a small dot in it.
  4. In the Replaced by (Replace With) enter any value to insert in place of carriage returns. Usually a space is used for this to avoid accidental gluing of two adjacent words. If you just want to remove line breaks, leave the field Replaced by (Replace With) empty.How to remove line breaks (carriage returns) from cells in Excel 2013, 2010 and 2007
  5. Нажмите кнопку Replace all (Replace All) and enjoy the result!How to remove line breaks (carriage returns) from cells in Excel 2013, 2010 and 2007

Remove line breaks using Excel formulas

Pros: You can use sequential or nested formulas for complex text verification in the processed cell. For example, you can remove carriage returns and then find extra leading or trailing spaces, or extra spaces between words.

In some cases, line breaks must be removed in order to later use the text as function arguments without making changes to the original cells. The result can be used, for example, as a function argument VIEW (LOOKUP).

Cons: You will need to create a helper column and perform many additional steps.

  1. Add an auxiliary column at the end of the data. In our example, it will be called 1 lines.
  2. In the first cell of the auxiliary column (C2), enter the formula to remove/replace line breaks. Below are a few useful formulas for various occasions:
    • This formula is suitable for use with Windows and UNIX carriage return/line feed combinations.

      =ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;СИМВОЛ(13);"");СИМВОЛ(10);"")

      =SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),"")

    • The following formula is suitable for replacing a line break with any other character (for example, “, ” – comma + space). In this case, the lines will not be combined and extra spaces will not appear.

      =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;СИМВОЛ(13);"");СИМВОЛ(10);", ")

      =TRIM(SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),", ")

    • And this is how you can remove all non-printable characters from text, including line breaks:

      =ПЕЧСИМВ(B2)

      =CLEAN(B2)

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

  3. Copy the formula to all cells in the column.
  4. Optionally, you can replace the original column with a new one, with line breaks removed:
    • Select all cells in a column C and pressing Ctrl + C copy the data to the clipboard.
    • Next, select a cell B2, press the keyboard shortcut Shift + F10 and then Insert (Insert).
    • Delete the helper column.

Remove line breaks with VBA macro

Pros: Create once – use again and again with any workbook.

Cons: At least basic knowledge of VBA is required.

The VBA macro in the following example removes carriage returns from all cells on the 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 are not too familiar with VBA, I recommend that you study the article on how to insert and execute VBA code in Excel.

Leave a Reply